Solved

FILTER BY DATE FIELD sql 2005

Posted on 2009-04-06
6
496 Views
Last Modified: 2012-05-06
Hello Guys,

I am having problem to filter through datetime field

I was doing this:

Select * from Table
where convert(char(10),datefield,112)='20090403'

But doing this I will have my index off

The dates that I have recorded is in this format:

2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00
2008-04-21 20:00:00

I have a time together

How can I filter the rows ?
I tried this but it didn't work

select * from table
where datefield='2009-04-21'

None rows

Thanks
0
Comment
Question by:hidrau
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24076103
this should do:
Select * from Table
where datefield >= convert(datetime,'20090403', 112)
  and datefield < dateadd(day,1,convert(datetime,'20090403', 112))

Open in new window

0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24076113
This wont work

select * from table
where datefield='2009-04-21'

as the value '2009-04-21' will be converted into datetime value of '2009-04-21 00:00:00.000' which is not equal to your date value.

Using a scalar function convert will not use the Index on that column.
In order to use your index effectively your query should be something like this:

select * from table
where datefield between cast('2009-04-21' as datetime) and DATEADD (DD, 1, DATEADD(MS, -2, CAST('2009-04-21' as datetime)))
0
 
LVL 12

Expert Comment

by:udaya kumar laligondla
ID: 24076128
use
select * from table
where datediff(dd,datefield,'2008-04-01') = 0
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24076149
udayakumarlm,
   FYI, Even your query wont use the Index on the datefield column.
0
 
LVL 12

Expert Comment

by:udaya kumar laligondla
ID: 24076254
small correction
select datefield from table
where datediff(dd,datefield,'2008-04-01') = 0 will use index
not
select * from table
where datediff(dd,datefield,'2008-04-01') = 0
hidrau, if it is performace that you are looking at then don't use *, if the fields you specify instead of * are all indexed then SQL will use index scan else it will use clustered index scan.
0
 
LVL 1

Author Closing Comment

by:hidrau
ID: 31566965
thanks
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question