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.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan