T-SQL DateTime to Date Conversion - Tolerance

date-conversion, datetime, sql-server, t-sql

Solution

Because there's no .999 and .998 fraction of a second in datetime type. You only have .990, .993 and .997.

So .998 is rounded down to .997, while .999 is rounded up. Read more about the type.

Problem

We are attempting to strip the time off a DateTime variable: ``` DECLARE @Date DateTime SET @Date = '01Jan2013 23:59:59.998' PRINT DATEADD(dd, 0, DATEDIFF(dd, 0, @Date )) SET @Date = '01Jan2013 23:59:59.999' PRINT DATEADD(dd, 0, DATEDIFF(dd, 0, @Date )) ``` Result: Jan 1 2013 12:00AM Jan 2 2013 12:00AM Why does `01Jan2013 23:59:59.999` come back as 2nd Jan rather than 1st Jan?

Original source