If you’re getting SQL Server error Msg 529 that reads Explicit conversion from data type date to int is not allowed, it’s because you’re trying to explicitly convert a date data type to an int data type, which is not allowed in SQL Server.

To fix this issue, try converting the date value to a string first, and then to an integer.

Alternatively, change the destination type to one that’s allowed.

Also, check that you’re trying to convert the correct value. For example, you may have selected the wrong column or variable. In this case, selecting the correct column may fix the problem.

Example of Error

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

DECLARE @thedate date = '2035-01-02';
SELECT CONVERT( int, @thedate, 112 );

Result:

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

Here, we tried to convert a date type to an int type.

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

We still get the error when using a function like TRY_CONVERT() or TRY_CAST():

DECLARE @thedate date = '2035-01-02';
SELECT TRY_CONVERT( int, @thedate, 112 );

Result:

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

Although TRY_CONVERT() and TRY_CAST() 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 1

If we really want to convert a date to an integer, we can first convert the date to a string. Once that’s done, we can convert it to an int type.

Example:

DECLARE @thedate date = '2035-01-02';
SELECT CONVERT( int, CONVERT( nchar(8), @thedate, 112 ) );

Result:

20350102

Solution 2

Another option is to convert the value to a different data type. Actually, that’s what we did in the previous example before we converted it to the integer – we converted it to a string first, and then from a string to an integer. But we do have the option of leaving it as a string.

Example:

DECLARE @thedate date = '2035-01-02';
SELECT CONVERT( nchar(8), @thedate, 112 );

Result:

20350102

All I did here was remove the outer CONVERT() function from the previous example.

Solution 3

Sometimes the problem could be caused by a mistake on our part. For example, we could be trying to convert the wrong column or variable.

In such cases, the solution could be as easy as replacing the column or variable with the correct one.