DATETIME: Select * from table1 where date1 LIKE '%1/2/2004%' 1

[GeeWond3r]

I've attempted to query records using a datetime field, but unsuccessful.

Select * from table1 where date1 LIKE '%1/2/2004%'

Is there a sql query that allows you to pull the records based on a datetime search criteria??

 

[JayKusch]

Unless the date field was created as a Char or VarChar, this will not work. Especially if you have create that date field as a datetime datatype.

Try this ...

Select * from table1 where CONVERT(Char,date1,110) = '1/2/2004'


Thanks

J. Kusch

 

[MDXer]

Agree 99.999% with Jay except the the output format.

110 will return 1-2-2004
101 will return 1/2/2004

using 110 will result in no rows being returned

Change 110 to 101 or use mm-dd-yyyy syntax instead of mm/dd/yyyy

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[rsinj]

if there are a large number of records in the table, then you do not want to do a CONVERT on the column to compare with the constant as this is inefficient and will always result in a table scan. from an optimization/performance standpoint, you want to get your constants into the proper format and directly compare to the column (which presumably is indexed).

for instance, instead of:

Select *
from table1
where CONVERT(Char,date1,101) = '1/2/2004'

use :
Select *
from table1
where date1 >= '1/2/3004'
and date1 < '1/3/2004'

or, if you're a purist:
Select *
from table1
where date1 >= CONVERT(datetime,'1/2/3004')
and date1 < CONVERT(datetime,'1/3/2004')

 

[rsinj]

sorry - those 1/2/3004's should obviously be 1/2/2004