Solved

TSQL - DateTime column with comparison operators

Posted on 2008-10-06
18
1,429 Views
Last Modified: 2012-05-05
What's the correct syntax for the following WHERE clause?

WHERE [EventDate] >= '12-1-2008'
0
Comment
Question by:jdana
  • 7
  • 6
  • 2
  • +2
18 Comments
 
LVL 15

Accepted Solution

by:
rob_farley earned 250 total points
ID: 22655914
You should use YYYYMMDD for guaranteed results. So if you want Dec 1st, use:

WHERE EventDate >= '20081201'

This will work just fine (assuming that the EventDate column is using the datetime type).

Rob
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22655913
looks good, actually (if event date is a datetime field). Are you not getting any results?  You might try this:

select * from tablename
where eventdate >= '12/01/2008'
0
 
LVL 5

Expert Comment

by:adlink_la
ID: 22655916
Looks good to me.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22655920
Assuming EventDate is datetime, just make sure that the date literal you use can be interpreted as a valid
date, and that there is no ambiguity.  I like the ISO date:

WHERE EvenetDate >= '2008-12-01'
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22655924
too slow :)
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 22655928
Matthew - '2008-12-01' won't work if your language is set to French. '20081201' will work regardless.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22656152
>>Matthew - '2008-12-01' won't work if your language is set to French. '20081201' will work regardless.<<
Both will be fine.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22656164
So that SQL does not have to implicitly convert you can also do this:

WHERE [EventDate] >= CONVERT(datetime, '12/01/2008', 101)   -- Assuming US format.
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 22656254
ACPerkins,

But if you run:

set language french

...then '2008-12-01' won't work. And why force it to use 101 so that you can use a format that half the world thinks is wrong?

Rob (in Australia, where 12/01/2008 is in January)
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22656309
Rob,
The author is in mountain time...which is in the US, so Australia or French time really doesn't come into play.  From what I can see, matthewspatrick, myself, adlink_la, and acperkins are on the right track.
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 22656459
:) But my query works just fine wherever you are, and it's generally considered best practice to use the YYYYMMDD format.

I'm not saying that any of you are wrong, I'm just pointing out that there are additional issues with date formats that people should be aware of.

Rob
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22656489
>>But if you run:

set language french

...then '2008-12-01' won't work.
<<
Have you tried it?  I suggest you take the time to do so.

>>And why force it to use 101 so that you can use a format that half the world thinks is wrong?<<
I was not suggesting that.  Please note the comment: "Assuming".  If I had known they were in my home country than I would have suggested:

WHERE [EventDate] >= CONVERT(datetime, '12/01/2008', 103)

But it is a fair assumption, even without noticing the time zone, that on this website 80% do not live in Australia, let alone my home country so I resort to suggesting the US date format. (And no, I do not believe that half the world thinks it is wrong, it is just different as is dozen other date format.  That is analogous to repeating the tired joke that you drive on the wrong side of the road)
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 22656498
Yes, I have tried it. The date that it returns is in January. Try '2008-12-13' if you want.

Rob
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22656518
>>Have you tried it? I suggest you take the time to do so<<
It is obvious, that I did not take the time to fully test this out. I apologize you are correct.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22656527
So there you have it you can use an unambiguous ISO date format as suggested by a number here, in which case SQL Server will implicitly do a convert to compare the dates or you can explicitly convert the date using your regional style.

Pick your poison.
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 22656549
But if you use '20081201', it will always be considered the first day of the twelfth month, 2008.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22656590
Yes, we agreed on that. That was one option the author can take. If you have been here long enough you would know that some experts here such as angelIII prefer the second option in other words explicitly doing the CONVERT (and no angelIII is not from the US).

Now relax and let the author decide which is the one they like the best.
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 22656641
:) Don't worry - I'm relaxed.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

785 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