Solved

TSQL - DateTime column with comparison operators

Posted on 2008-10-06
18
1,428 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
 
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now