Conversion Failed When Converting Date /Time from Character String in SQL Server

Recently, I worked with date and time data types in SQL Server. I tried to convert a character string including date into Datetime using the CAST() function, and I faced an error Conversion failed when converting date and/or time from a character string. I resolved the error and sharing here how I did.

This tutorial explains the reason for that error and how to resolve it in SQL Server.

Solution: Conversion Failed When Converting Date/Time from character string in SQL Server

I was also trying to execute the T-SQL query below.

SELECT CAST('12 10 2020' AS DATETIME)

Below is my error,

I figured out that I was not specifying the date in the correct format. I was trying to use the space character as the separator, which is invalid in SQL Server. Then, I found a few separators that could be used. I used one of them and resolved the error. The new query was:

SELECT CAST('12-10-2020' AS DATETIME)

The query got executed successfully and I got the date in DateTime data type format.

This was the solution to my problem. However, you may also face this error due to other errors in the format. Some of the examples are shown below:

• Incorrectly spelled words in date format

For example:

SELECT CAST('12 Febrary 2020' AS DATETIME) AS [United States Datetime]

• Missing a part of the date

For example:

SELECT CAST('12-2020' AS DATETIME) AS [United States Datetime]

• Date specified in a different language

For example:

SELECT CAST('25 de Diciembre 2021' AS DATETIME) AS [United States Datetime]

• Missing or incorrect date separators

SELECT CAST('12 10 2020' AS DATETIME) AS [United States Datetime]

SELECT CAST('12102020' AS DATETIME) AS [United States Datetime]

Note: You can also specify the date without any separator, but you have to use the date format as “YYYYMMDD“. For example:

SELECT CAST('12102020' AS DATETIME) AS [United States Datetime]

Hence, these were some possible reasons behind the error “Conversion failed when converting date and/or time from character string” in SQL Server.

Video Tutorial

Conclusion

Now, you have an idea of the reason for this error and how to solve it. Also explained some of the examples of the reason for the error. Make sure you are using the correct query.

You may also like to read the following SQL Server tutorials.

• SQL Server Convert Function + Examples
• SQL Server Convert Datetime to String + Examples
• Error: 40 – could not open a connection to sql server
• The conversion of a date data type to a DateTime data type resulted in an out-of-range value SQL