SQL Server: Resolving DateTime Conversion Errors

Sometimes your queries may return errors when using DateTime values. Consider the following select statement

select cast('19/12/2000' as datetime)

This statement will fail with the following error message:

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The reason is that SQL Server either does not recognize the input date format or the date is invalid. It is important to know the Server's date setting before using any date formats in your queries.

There is a DBCC command which can be used to determine the current date format

DBCC USEROPTIONS

When you run the code, the third row gives you the current dateformat. It is mdy on my machine, so you should input dates in mm-dd-yyyy format. If it is dmy, you should input it in dd-mm-yyyy format. The input format in most cases, should be based on the server's date format.

Now when you run the following code by formatting dates in mdy format (in sync with your machine format), it works fine:

select cast('12/19/2000' as datetime)



Note: It is also better to use an unambiguous date format YYYYMMDD HH:MM:SS which will work for all date settings.