Solved

FILTER BY DATE FIELD sql 2005

Posted on 2009-04-06
6
486 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 142

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:udayakumarlm
ID: 24076128
use
select * from table
where datediff(dd,datefield,'2008-04-01') = 0
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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:udayakumarlm
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

777 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