Implicit Conversion From Varchar to Datetime

Sometimes a simple little thing may bring us some sights. Today I'd like to share with you something related to implicit conversion in sql server.

Problem

A colleague brought me this scenario: when she tried to update some table by joining with other tables, She got the following error:

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

So the action to work around this is to change data type of the related column from varchar(10) to datetime by just using ALTER COLUMN function. However, another error jumped out:

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

The statement has been terminated.

What? This is kind of common practices for me as well. What did the error come from?

So I went ahead to dig into the date column that was defined as varchar(10). The moment I saw the data, I got some hint. In that column, the date was displayed as "DD-MM-YYYY". 

Solution

After changing the format from "DD-MM-YYYY" to "YYYY-MM-DD" by the following script:

UPDATE schemaName.tableName

SET datecolumn = SUBSTRING(datecolumn,7,4)+'-'+ SUBSTRING(datecolumn,4,2)+'-' +SUBSTRING(datecolumn,1,2)

there is no more problem to use ALTER COLUMN to update that column's data type from Varchar to Datetime. 

Conclusion

 As a summary, SQL Server only allows implicit conversion from Varchar to Datetime when the date format is "YYYY-MM-DD". If you want to learn more about implicit/explicit data conversion, please read technet's article: Data Type Conversion.