Friday, December 01, 2006

SQL Server Date Trick

Need an easy way to truncate a SQL Server DateTime to just the date part (i.e., midnight of the day that is represented)?

You can cast a DateTime as a float, take the floor value of that in order to get an unrounded integer, and then cast that back to a DateTime. 

cast(floor(cast(@fromDate as float)) as datetime)

This works because almost any modern platform's datetime value is a floating point value that represents a number of days since an epoch date (with the fractional part representing the time of day).  Negative numbers are days prior to the epoch, etc.  So, trim off the fraction, and you have just days.

This is much cleaner than another way that I found in some stored procedure code which essentially subtracts the hours, minutes, seconds, and fractions of a second from a given DateTime...