Error Message:
Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
Severity level:
16.
Description:
This error message appears, when SQL Server cannot convert a character string into a valid SMALLDATETIME value.
Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.
Resolution:
Error of the Severity Level 16 are generated by the user and can be fixed by the SQL Server user. When converting into a SMALLDATETIME data type, the value must be within the valid range for this type.
Versions:
All versions of SQL Server.
Example(s):
SELECT CAST(‘18991231’ AS SMALLDATETIME)
Remarks:
In the above example the string ‘18991231’ is beyond the valid range of values for the SMALLDATETIMEdata type. As soon as the value is within the valid range, SQL Server automatically takes care of the conversion. To avoid any suprises, one needs to be aware of the rounding habits for the SMALLDATETIME data type. Because this type has only a precision of 1 minute, SQL Server rounds seconds (incl. Milliseconds) > 30.001 up to the nearest higher full minute. Values < 29.997 are rounded down to the nearest lower full minute.