If the [date] column in the database contains no time elements then, say, 22-May-2006 would be stored as midnight (00:00:00). In that case you wouldn't need to do the 23:59:59 (or 23:59 on a SMALLDATETIME) thing. It has confused me why there isn't a useful built-in date only function (to extract just the date part) for comparisons such as this. Solutions I've used in the past include:
-- Performance is okay
...WHERE DAY([date]) = 22 AND MONTH([date]) = 5 AND YEAR([date]) = 2006
-- Performance is abysmal on large datasets and useless if part of a join (What was I thinking!?!)
WHERE [date] = dbo.datetime_rounddown(@someDateTime)
-- Okay as a replacement for a column that is being SELECTed
SELECT dbo.datetime_rounddown([date]) FROM MyTable
-- If using a temp table to add an extra column that specifically excluded the time portion
-- Or if the table is sufficiently large, you could add an extra column to hold the date only version
I've never tried to convert it to an varchar and use that so I don't know what the performance is like, but it would have to be the ISO format because greater/less-than comparisons wouldn't work properly, e.g. (using the British format) 22/05/2006 22/05/2007 -- Jump a year 23/05/2005 -- then jump almost two years back again. You'd get a similar situation with the US format date too. If the comparison is something that is going to be done a lot and the table is large, then it may be worth considering denormalising the data model slightly to boost the performance. I've done that before where the table was holding millions of rows. Some queries went from taking several minutes to taking a few seconds.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
