Solved

FILTER BY DATE FIELD sql 2005

Posted on 2009-04-06
6
493 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
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
Independent Software Vendors: 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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can i use WITH CTE for checking exist value? 3 55
convert null in sql server 12 57
Substring works but need to tweak it 14 31
Using this function 4 41
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 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