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...
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Mark WillsConnect With a Mentor Topic AdvisorCommented:
datediff can work, and converting last_update_date can work, but will result in table scans because you are changing the data basis / effectively creating a runtime computed field. Not always bad, but generally on large tables / results sets can cause a couple of problems...

The challenge you have is removing or accommodating the time component. style code 120 also contains a time component, so possible not the best choice of style code unless you limit by length - and char is a better choice than varchar.

Better still is to make sure you always match the datatype of the underlying table source data.

assuming '2009-01-23' is a parameter of some description, then need to use style code 120 to match the layout of that string :

WHERE last_update_date between CONVERT(DATETIME, '2009-01-23', 120)  and CONVERT(DATETIME, '2009-01-23'  + ' 23:59:59.997', 120)

Or, if part of a procedure, then fix up that string first :

declare @sd datetime
declare @ed datetime
set @sd = CONVERT(DATETIME, '2009-01-23', 120)
set @ed = CONVERT(DATETIME, '2009-01-23'  + ' 23:59:59.997', 120)

then your where clause is ALL datebased and will work much better :

WHERE last_update_date between @SD and @ED

BUT, if you care to elaborate a bit more on how that '2009-01-23'  is actually being generated, then might be able to give you a few more pointers. For example, did you know that yyyymmdd and yyyy-mm-dd are both implictly understood in a convert statement and no real need for any style code ?

select convert(datetime, '2009-03-23' + ' 23:59:59.997', 120), convert(datetime, '2009-03-23' + ' 23:59:59.997'), convert(datetime, '20090323' + ' 23:59:59.997')

Also in SQL2008 you have the DATE construct which ignores the time component - and a whole lot more - and might change the approach ?

SELECT  CAST('2009-01-23 23:59:59. 1234567' AS time(7)) AS 'time'
    ,   CAST('2009-01-23 23:59:59. 1234567' AS date) AS 'date'
    ,   CAST('2009-01-23 23:59:59.123' AS smalldatetime) AS 'smalldatetime'
    ,   CAST('2009-01-23 23:59:59.123' AS datetime) AS 'datetime'
    ,   CAST('2009-01-23 23:59:59. 1234567 +12:15' AS datetime2(7)) AS 'datetime2'
    ,   CAST('2009-01-23 23:59:59.1234567 +12:15' AS datetimeoffset(7)) AS 'datetimeoffset'

David H.H.LeeCommented:
Hi isaackhazi,
Try DATEDIFF() function.
where DATEDIFF(day, last_update_date, '2009-01-23 00:00:00.0000000')=0

Pratima PharandeCommented:
try this

Convert(datetime, last_update_date, 120)= convert(datetime, '2009-01-23', 120)
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Luan JubicaProject ManagerCommented:
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 "="
isaackhaziAuthor Commented:
so what do i have to use instead of 120??
Luan JubicaProject ManagerCommented:
use 101 ?
Luan JubicaProject ManagerCommented:
or as suggested above Datediff is a valid solution.........
udaya kumar laligondlaTechnical LeadCommented:
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))

Open in new window

All Courses

From novice to tech pro — start learning today.