isaackhazi
asked on
SQl query with date
I use the following syntax to filter by date. It works great when I use the '<' or '>' opertaors on the dattime datatype column. What i want is to get all the records with a specific day(date) say 03/10/09. when i add a '=' operator i get no records.
where last_update_date < convert(datetime, '2009-01-23', 120)
but when i add this
last_update_date = convert(datetime, '2009-01-23', 120)
i get 0 results.
can anyone rectify this or give me an alternative...
where last_update_date < convert(datetime, '2009-01-23', 120)
but when i add this
last_update_date = convert(datetime, '2009-01-23', 120)
i get 0 results.
can anyone rectify this or give me an alternative...
try this
Convert(datetime, last_update_date, 120)= convert(datetime, '2009-01-23', 120)
Convert(datetime, last_update_date, 120)= convert(datetime, '2009-01-23', 120)
when you use convert(datetime, '2009-01-23', 120) it means '2009-01-23 00.00.000'
so every other date like '2009-01-23 09.11.000' so including the exact time will be different, so you got no results on "="
so every other date like '2009-01-23 09.11.000' so including the exact time will be different, so you got no results on "="
ASKER
so what do i have to use instead of 120??
use 101 ?
or as suggested above Datediff is a valid solution.........
use CONVERT(varchar(8), last_update_date, 112) = CONVERT(varchar(8), '2009-01-23', 112)
Although udayakumarlm's solution works this means any available indexes will not be used. This solution makes allowes for indexes to be used.
WHERE last_update_date >= CONVERT(DATETIME, '2009-01-23', 120)
AND last_update_date < DATEADD(DAY, 1, CONVERT(DATETIME, '2009-01-23', 120))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try DATEDIFF() function.
http://msdn.microsoft.com/en-us/library/ms189794.aspx
eg:
...
where DATEDIFF(day, last_update_date, '2009-01-23 00:00:00.0000000')=0