If you’re getting SQL Server error Msg 529 that reads something like Explicit conversion from data type int to date is not allowed, it’s because you’re trying to perform an explicit data type conversion that’s not permitted.

SQL Server doesn’t allow certain conversions. If you try to perform such a conversion, you’ll get this error.

In this case, the error indicates that we’re trying to convert from an int to a date type, which SQL Server doesn’t allow.

To fix this issue, make sure you’re passing an allowed data type to be converted. In some cases, we can convert the number to a string before converting to a date. This all depends on the actual number we’re trying to convert.

Example of Error

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

SELECT CAST( 20351231 AS date );

Result:

Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type int to date is not allowed.

Here, we tried to convert the number 20351231 to the date type.

SQL Server doesn’t allow such a conversion and so it returned an error.

Even the TRY_CAST() function doesn’t work in this case:

SELECT TRY_CAST( 20351231 AS date );

Result:

Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type int to xml is not allowed.

Although the TRY_CAST() function can normally be used to return NULL instead of an error, that doesn’t apply for this particular error.

When a conversion fails due to the conversion not being permitted, then even TRY_CAST() (and TRY_CONVERT()) will return an error.

Solution

To fix this, you’d need to change either the original type or the destination type (or both).

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.

If you’re sure it’s the right column or variable, and you’re sure the number correctly represents the date that you’re trying to convert to, then try first converting the number to a string, before converting it to a date:

SELECT CAST( CAST( 20351231 AS char(8)) AS date );

Result:

2035-12-31

Here, the int has successfully converted to char(8), and then to a date.

We can also do the following:

SELECT CAST( '20351231' AS date );

Result:

2035-12-31

In that case, I passed the number as a string literal. Doing this eliminated the need to explicitly convert the number to a string first.

The above solutions only work on the provision that the number is in the right format, so that it can be translated into a date. For example, the following operation fails:

SELECT CAST( '20351232' AS date );

Result:

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

In this case I incremented the day portion so that it was 32. This isn’t a valid day, and so the conversion failed with a different error (error 241).