Comparing dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Comparing dates

Author

Topic

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-07-31 : 14:24:39

Is it a good idea to compare dates this way? Or is there a better way that won't require like 5 lines of comments explaining what's going on in here.

SELECT OrderDate FROM Orders WHERE CONVERT(VARCHAR(20), OrderDate, 102) = '1996.07.04'

I came across this recently, and being the SQL newb that I am it looked a little different...
--Nick

Kristen
Test

22859 Posts

Posted - 2006-07-31 : 14:35:00

If OrderDate is already in a datetime then you can just compare against text string presented as 'yyyymmdd' - note that there is NO punctuation, this format will be treated by SQL Server un-ambiguously (there are many other date formats which are acceptable to SQL Server, but they depend on the locale settings of the server, the usage of SET DATEFORMAT and so on, and therefore are at risk from any of those environmental parameters changing)

If you OrderDate also includes a time then its a bit more tricky. The most efficient way to get JUST the date is:

DATEADD(Day, DATEDIFF(Day, 0, MyDateColumn), 0)

but it probably takes 5 lines of comments to explain like you said

You could put that in a function.

If OrderDate is indexed (or might be in the future) then it is important not to use a Function on it - as that will (usually) prevent SQL Server using an index.

So instead of:

SELECT OrderDate FROM Orders WHERE CONVERT(VARCHAR(20), OrderDate, 102) = '19960704'

you should consider using:


SELECT OrderDate FROM Orders 
WHERE     '19960704' <= OrderDate 
      AND OrderDate < '19960705'


SELECT OrderDate FROM Orders 
WHERE     '19960704' <= OrderDate 
      AND OrderDate < DATEADD(Day, 1, '19960704')

Kristen

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-07-31 : 14:55:34

Pretty cool. I didn't know you could compare dates using a string like that, thanks.
--Nick

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-01 : 11:56:38

or

Where OrderDate>='19960704' and OrderDate<'19960705'

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-08-02 : 04:06:32

"Where OrderDate>='19960704' and OrderDate<'19960705'"

That's what I said - or are you running your optimising-parser on my code?!!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-02 : 11:19:26

It is just a modified code of your first method

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources