How to fix “conversion failed when converting date and/or time from character string”

Bad data – it’s everywhere and a common problem is badly formatted dates. “conversion failed when converting date and/or time from character string” is the message returned in such situations.

It’s not unique to SQL and will be seen in other languages such as Python, C#, Java and others.

In this post, we look at the why and how you can fix it.

Getting to the bottom of why “conversion failed when converting date and/or time from character string” occurs

So this is an attempt by the code to convert a string that contains a date or time data type value but the conversion fails due to incorrect formatting or other issues.

Common reasons for this include:

1. Incorrect date/time format: The string value being converted does not match the expected format for a date or time value. For example, if the expected format is “yyyy-mm-dd” but the string contains “dd-mm-yyyy”, the conversion will fail.
2. Invalid characters: The string value contains characters that are not valid for a date or time value. For example, if the string contains letters or symbols that are not part of the expected format, the conversion will fail.
3. Null or empty strings: The string value is null or empty, which cannot be converted into a date or time data type.
4. Data type mismatch: The string value is being converted into a data type that is not compatible with the string value. For example, if the string value contains a time value but is being converted into a data type that only accepts dates, the conversion will fail.

This can be either a data or code issue. Resolving the matter should be easy to spot, especially when debugging code. Sometimes it can be harder if the database engine isn’t giving you an idea as to which row is affecting the query.

SELECT CAST("30-03-1976" AS DATE)

May return the following error:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

I say may because it depends on the database engine. The above message was returned from SQL Server but in MySQL for instance, it will execute the query but NULL the output which you have to be careful with because ideally you want to fix the issue, not let it escape as a NULL.

Solutions

1. Fix the data
2. Error handle it
3. Check the data type is correct

Solution 1 – Fixing the data

So, changing ’30-03-1976′ to the expected format will remedy the issue. If the format is yyyy-mm-dd, then update the value in the data to be ‘1976-03-30’

Solution 2 – Error handle it

As I mentioned, MySQL handles it automatically and NULL’s it. SQL Server is more strict so you can use some handling functions to produce the same effect. The following use of TRY_CAST() will return NULL.

SELECT TRY_CAST('30-03-1976' AS DATE);

With this improvement, at least the code will not fall over and it would then be possible to query for NULL instances of the data for review later.

Solution 3 – Check the data type is correct

There is a remote possibility that you set up the query incorrectly and specified the wrong data type in the CAST function. So do check that the data type you are casting to is what you want the output to be.

I hope you found this post useful 🙂