Conversion failed when converting date and/or time from character string in SQL Server

Recently, I was working with date and time data types in SQL Server I was trying to understand how these data types work.

I tried to convert a character string including a date into a DateTime data type value using the CAST() function. I was trying to execute the below T-SQL query:

SELECT CAST('12 10 2020' AS DATETIME)

At that time, I faced the error “Conversion failed when converting date and/or time from character string“.

Solution: Conversion failed when converting date and or time from character string in SQL Server

I tried to find the solution to this problem on the web as I wanted to know what the problem was. 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 not valid 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 Febraury 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.

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

• SQL Server Convert Function + Examples
• SQL Server Trigger Before Insert
• SQL Server Convert String to Date + Examples
• SQL Server Convert Datetime to date + Examples
• SQL Server convert integer to string + 12 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