Solved

TSQL - DateTime column with comparison operators

Posted on 2008-10-06
18
1,430 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

820 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