Monday, September 21, 2020

SQL: Get just the date part of a DATETIME on SQL Server 2005

In Microsoft SQL Server 2008+ there is a DATE type, and getting just the date portion of a DATETIME is as easy as a cast or convert to DATE. But, at my job, we're still using SQL Server 2005. What then?

A common technique is to cast to a float, which gives you a number where the part to the left of the decimal point is the date and the part to the right of the decimal point is the time. Wrap that in a FLOOR function to round down to the nearest whole number and you have effectively truncated the time portion. You can then cast that back to a DATETIME.

I have, however, read that doing it that way can be inefficient as it won't use DATETIME indexes. Another approach that it supposed to be more efficient is to use DATEADD and DATEDIFF like so:


Again, there is no reason to do that unless you are on a Microsoft SQL Server version prior to 2008.

No comments:

Post a Comment