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...
LVL 8
isaackhaziAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David H.H.LeeCommented:
Hi isaackhazi,
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

0
Pratima PharandeCommented:
try this

Convert(datetime, last_update_date, 120)= convert(datetime, '2009-01-23', 120)
0
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 "="
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

isaackhaziAuthor Commented:
so what do i have to use instead of 120??
0
Luan JubicaProject ManagerCommented:
use 101 ?
0
Luan JubicaProject ManagerCommented:
or as suggested above Datediff is a valid solution.........
0
udaya kumar laligondlaTechnical LeadCommented:
use CONVERT(varchar(8), last_update_date, 112)  = CONVERT(varchar(8), '2009-01-23', 112)  
0
lexiflexCommented:
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

0
Mark WillsTopic 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'




0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.