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 th开发者_Python百科e changed in the past? Is there an even better way to compare the short form of dates?

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'

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.

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())))