Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1439
  • Last Modified:

TSQL - DateTime column with comparison operators

What's the correct syntax for the following WHERE clause?

WHERE [EventDate] >= '12-1-2008'
0
jdana
Asked:
jdana
  • 7
  • 6
  • 2
  • +2
1 Solution
 
rob_farleyCommented:
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
 
chapmandewCommented:
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
 
adlink_laCommented:
Looks good to me.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Patrick MatthewsCommented:
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
 
Patrick MatthewsCommented:
too slow :)
0
 
rob_farleyCommented:
Matthew - '2008-12-01' won't work if your language is set to French. '20081201' will work regardless.
0
 
Anthony PerkinsCommented:
>>Matthew - '2008-12-01' won't work if your language is set to French. '20081201' will work regardless.<<
Both will be fine.
0
 
Anthony PerkinsCommented:
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
 
rob_farleyCommented:
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
 
chapmandewCommented:
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
 
rob_farleyCommented:
:) 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
 
Anthony PerkinsCommented:
>>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
 
rob_farleyCommented:
Yes, I have tried it. The date that it returns is in January. Try '2008-12-13' if you want.

Rob
0
 
Anthony PerkinsCommented:
>>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
 
Anthony PerkinsCommented:
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
 
rob_farleyCommented:
But if you use '20081201', it will always be considered the first day of the twelfth month, 2008.
0
 
Anthony PerkinsCommented:
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
 
rob_farleyCommented:
:) Don't worry - I'm relaxed.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 6
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now