If you’re getting SQL Server error Msg 241 that reads Conversion failed when converting date and/or time from character string, it’s probably because you’re trying to convert a string to a date/time value, but that particular string can’t be converted to the date/time type specified.

Example of Error

Here’s an example of code that produces the error:

SELECT CAST('Tomorrow' AS date);

Result:

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

Here, we tried to convert the string Tomorrow to the date type, but Tomorrow is not a valid date that can be converted to a date type.

SQL Server can’t convert the string to a date and so it returned an error.

Solution 1

The most obvious way to fix this is to change the string to resemble an actual date. Or if it does resemble a date, change the string literal to a format that can be converted to the particular date/time type that we’re trying to convert it to.

If you’re passing a column, check that you’ve got the right column. Same if you’re passing a variable – check that it’s the right variable.

For example, the following conversion succeeds:

SELECT CAST('20 Feb 2030' AS date);

Result:

2030-02-20

Depending on the format of the input date, you might be better off using the CONVERT() function, so that you can specify the format of the input date expression.

Example:

SELECT 
    CONVERT(date, '03/02/2030', 101) AS 'US with century',
    CONVERT(date, '03/02/30', 1) AS 'US without century',
    CONVERT(date, '03/02/2030', 103) AS 'British with century',
    CONVERT(date, '03/02/30', 3) AS 'US without century';

Result:

+-------------------+----------------------+------------------------+----------------------+
| US with century   | US without century   | British with century   | US without century   |
|-------------------+----------------------+------------------------+----------------------|
| 2030-03-02        | 2030-03-02           | 2030-02-03             | 2030-02-03           |
+-------------------+----------------------+------------------------+----------------------+

Solution 2

Another way to deal with the error is to use either TRY_CAST() or TRY_CONVERT(). These functions return NULL instead of the error.

Example:

SELECT TRY_CAST('Tomorrow' AS date);

Result:

NULL

This doesn’t necessarily address the underlying issue though, it merely hides it. The conversion still couldn’t take place.

In any case, it may still be a viable option, depending on your needs.

Solution 3

Another way to deal with the error is to convert the string to a different data type. Obviously, this is no good if you want to end up with a date type, but perhaps the error came about because you accidentally specified date type instead of the other type.

Example:

SELECT CAST('Wednesday' AS char(3));

Result:

Wed