Why do I get datetime conversion out of range for 999 milliseconds?

user_4308607 0
Viewed 2793

Could someone please explain me, why I get the error

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

when I execute the following code on SQL Server 2017. I get the error only for .999 milliseconds

SELECT CAST('9999-12-31 23:59:59.999' AS DATETIME)

Result:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Then I tried:

SELECT CAST('9999-12-31 23:59:59.998' AS DATETIME)

Result: 9999-12-31 23:59:59.997

Then I tried:

SELECT CAST('9999-12-31 23:59:59.997' AS DATETIME)

Result: 9999-12-31 23:59:59.997

sql-server-2017 datetime sql-server
4 Answers

Based on documentation the datetime range is between January 1, 1753 and December 31, 9999. Also still based on documentation the time range is between 00 to 23:59:997.

So if you make a rounding, at 998 it will round down to 997. At 999 it should round up to January 01 10000 which is out of range. (the precision of datetime in sql-server is 3.33ms)

user_6660880

Sometime (did I mean always ?) reading the documentation helps a lot.

It states :

Date range : January 1, 1753, through December 31, 9999

Time range : 00:00:00 through 23:59:59.997

Concerning the time being valid for 23:59:59.998, even if the valid range is up to 997 ms, this is still explained in the documentation

Times from 23:59:59.995 to 23:59:59.998 are stored as 23:59:59.997

Time 23:59:59.999 is stored as 00:00:00.000 +1 day

Then, 9999-12-31 23:59:59.999 is stored as 10000-01-01 00:00:00.000 which is out of range

user_83985490

datetime is only accurate to 1/300 of a second, therefore it only shows times that are accurate to .000, .003 and .007 of a second (the last is 2 thirds of a second rounded to 3 decimal places).

For your value '9999-12-31 23:59:59.999' SQL server can't store the 0.009, so it rounds to the closest value, in this case that would be the next second, making the date '10000-12-31 00:00:00.000', which can't be stored in any of the date and time data types.

When you have .998 the closest value to round to is .997, so the error does not occur.

If you use a datetime2 you won't receive the error either, as that can be accurate to 1/10000000 of as second: SELECT CAST('9999-12-31 23:59:59.999' AS datetime)

user_20299830

According to the documentation, the hundred of second are rounded like this :

  • 999 -> 000 rounded up (which cause your error)
  • 998, 997, 996, 995 -> 997
  • 992, 993, 994 -> 993
  • 991, 990 -> 990

This is because the datetime data type is not precise enough to store those values. You can use datetime2 if you need more precision.

If you like to understand how the datetime is stored in SQL Server, I recommend this article : https://www.red-gate.com/simple-talk/sql/t-sql-programming/how-to-get-sql-server-dates-and-times-horribly-wrong/

user_81654790
Related