CODEWITHSUNDEEP
sqlsql-server-2005datetime
enamrik
enamrik

Reputation: 2312

Proper Way To Compare Short Dates (SQL Server 2005)

Currently I'm comparing short dates this way:

CONVERT(datetime,CONVERT(VARCHAR(10), return_date,101)) = '{0}'

My supervisor says that I should do it this way:

datepart(month,return_date)= 5   
and  datepart(day,return_date)=24   
and  datepart(year,return_date)=2011

because the defined value 101 recognized by the function CONVERT might change in the future. Is that true? How often do these parameter values change? Have the changed in the past? Is there an even better way to compare the short form of dates?

Upvotes: 1

Views: 1498

Answers (3)

James Hill
James Hill

Reputation: 61832

This is my preference:

SELECT foo
FROM bar
WHERE return_date = CONVERT(datetime, FLOOR(CONVERT(float, Getdate())))

or, if return_date has a time stamp

SELECT foo
FROM bar
WHERE CONVERT(datetime, FLOOR(CONVERT(float, return_date))) = CONVERT(datetime, FLOOR(CONVERT(float, Getdate())))

Upvotes: 0

Thomas
Thomas

Reputation: 64655

If by "short dates" you mean "just the date without the time"

Select ...
From MyTable
Where return_date >= '20110524'
    And return_date < DateAdd(d, 1, '20110524')

First, note the < in the second part of the Where clause. Second, if there is an index on return_date, this approach will use it. Another solution which would not utilize an index (and thus result in a table scan) would be:

Select ...
From MyTable
Where DateAdd(d, DateDiff(d, 0, return_date), 0) = '20110524'

If return_date is always stored without its time component (i.e., its time component is always midnight) then you can do a simple comparison:

Select ...
From MyTable
Where return_date = '20110524'

Upvotes: 3

Nicholas Carey
Nicholas Carey

Reputation: 74307

Outside of adding conversion styles, convert() and its conversion styles haven't changed since SQL Server 4.2, c. 1992, when it was bundled with OS/2.

Upvotes: 1

Related Questions