[Tuning] Getting only the DATE from a datetime

Many ways to handle dates, and most time I don't think about it - then came across a gigantic thread over two years that discussed it.


Comparing dates. Traditionally, for simplicity, to mark particular dates, I've used convert(char(8),a_date_field,12), but it’s non-SARGable, and not terribly efficient.


Looking online I found a couple alternatives, though they’re mostly useful when having to deal with large tables.

http://blog.sqlauthority.com/2008/10/18/sql-server-retrieve-select-only-date-part-from-datetime-best-practice-part-2/

For SQL Server 2008 it's pretty simple:

SELECT  cast(GETDATE() as date)
or
SELECT CONVERT(date,GETDATE())

(date is a new datatype that only stores the date, and is 3 bytes in size)

For SQL Server 2005, there seem to be two options. Naturally, neither is SARGable from what I’ve seen.

SELECT CONVERT (datetime, FLOOR(CONVERT (float, a_field_name)))
and
SELECT DATEADD ( DAY, DATEDIFF(DAY, 0, a_field_name), 0)

The first one performs math on the internally-stored FLOAT value of the datetime - right now, for instance, is 40315.37003125, where the whole number is the day and the fraction is the time. (Note that you can’t cheat and use INT; it can round up during the conversion).

The second one does date manipulation - there's a whole article online about using datediff and dateadd and the like to get the first day of the month, the first day of last month, etc, etc.

From what I’ve seen online, the FLOOR option can be slightly faster than the DATEADD, but either is considerably faster than CONVERT(char(8)).