Solved

TSQL - DateTime column with comparison operators

Posted on 2008-10-06
18
1,427 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 14

Accepted Solution

by:
rob_farley earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Looks good to me.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
Comment Utility
too slow :)
0
 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
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
Comment Utility
>>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
Comment Utility
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 14

Expert Comment

by:rob_farley
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
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 14

Expert Comment

by:rob_farley
Comment Utility
:) 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
Comment Utility
>>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 14

Expert Comment

by:rob_farley
Comment Utility
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
Comment Utility
>>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
Comment Utility
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 14

Expert Comment

by:rob_farley
Comment Utility
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
Comment Utility
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 14

Expert Comment

by:rob_farley
Comment Utility
:) 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)

Join & Write a Comment

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

763 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

11 Experts available now in Live!

Get 1:1 Help Now