Truncate SQL Server Time

Today we needed to truncate the time portion of a DATETIME field.  In the past I’ve always used the method of converting it to a FLOAT, truncating the fraction and then converting the result back to a DATETIME.

SELECT CAST(FLOOR(CAST(GetDate() AS FLOAT)) AS DATETIME)

This works because when you convert the DATETIME to a FLOAT the integer portion contains the date and the fractional portion contains the time. The call to FLOOR truncates the fractional portion, leaving only the integer representation of the date.

However, if you are worried about performance, there is a faster way.

SELECT DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0)

The DATEDIFF(dd, 0, GetDate()) will get the number of days since 0, SQL Server’s minimum DATETIME value. Adding that back to 0 using DATEADD will result in the date without the time portion.

I like the DATEADD method better than the FLOAT method and will use it from now on.

  1. For maintainability, the DATEADD method is much clearer.
  2. When you convert a DATETIME to a FLOAT you lose some precision, which is okay when you are truncating.  Using the second method prevents the unintended use of a FLOAT conversion when precision does matter.
  1. Carol says:

    You are a natural genius!

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>