CODEWITHSUNDEEP
sql-serverdatetimesql-server-2012type-conversion
BTurkeli
BTurkeli

Reputation: 109

Explicit conversion from data type date to int is not allowed

I am trying to calculate AGE of contracts, however I am getting the error

Explicit conversion from data type date to int is not allowed

This is my code:

SELECT 
    Contract_hist.*,
    IB.*,
    CAST((CASE
             WHEN (CAST(CONVERT(INT, Setup.[Start]) - CONVERT(INT,[IB].[Install_Date])) AS INT))/365 <= 0 
                THEN 0  
             WHEN (CAST(CONVERT(INT, Setup.[Start]) - CONVERT(INT,[IB].[Install_Date])) AS INT)) / 365 > 0 
                THEN (CAST(CONVERT(INT, Setup.[Start]) CONVERT(INT,[IB].[Install_Date])) AS INT)) / 365 
          END) AS INT) AS AGE
FROM 
    Setup, Contract_hist
INNER JOIN 
    IB ON Contract_hist.[System No] = IB.System_ID

Where "Setup.[Start]" & "[IB].[Install_Date]" are datetime2 values.

Upvotes: 3

Views: 67249

Answers (3)

LukStorms
LukStorms

Reputation: 29677

You could convert those datetime2 fields first to datetime, then to an int. 

(convert(int, convert(datetime, Setup.[Start])) - convert(int,convert(datetime, [IB].[Install_Date])))/365

But that can be shortened to:

cast(convert(datetime, Setup.[Start]) - convert(datetime, IB.[Install_Date]) as int)/365

Or you could simplify it even more and calculate the difference in years directly with datediff:

datediff(year, [IB].[Install_Date], Setup.[Start])

And that CASE can also be simplified:

case 
when datediff(year, [IB].[Install_Date], Setup.[Start]) > 0 
then datediff(year, [IB].[Install_Date], Setup.[Start])
else 0
end as AGE

Upvotes: 4

TriV
TriV

Reputation: 5148

Maybe this?

SELECT 
Contract_hist.*
,IB.*
,CASE 
        WHEN DATEDIFF(year,[IB].[Install_Date], Setup.[Start] ) <= 0 THEN 0
        ELSE DATEDIFF(year, [IB].[Install_Date], Setup.[Start])
 END AS AGE

FROM Setup, Contract_hist
    INNER JOIN IB ON Contract_hist.[System No] = IB.System_ID

Upvotes: 2

ivanibash
ivanibash

Reputation: 1491

Seems like casting to a float/int is not supported in the latter versions of sql server. Try using DATEDIFF(dd, '12/30/1899', mydatefield)

Upvotes: 1

Related Questions