Calculations involving dates and integer

Question

I am working on SQL Server and have been trying to solve this question. How many more years does each employee have to work before reaching sentiment, if sentiment age is 65?

SELECT 
    BusinessEntityID,  DATEDIFF (year, BirthDate, ModifiedDate) AS 'Age', DATEDIFF (year, 60, 'Age') AS 'Age to Sentient'
FROM
    HumanResources.Employee

I expected to have three columns of BusinessEntityID, Age and Age to Sentiment. But I keep getting this error message:

Conversion failed when converting date and/or time from character string.

Answer 1

The challenge here is this statement: DATEDIFF (year, 60, 'Age'). From an SQL Server Perspective, you are asking it to use the string 'Age', when it is expecting a date.

If you want the 3 columns alone, then try something like this:

SELECT 
    BusinessEntityID, 
    DATEDIFF (year, BirthDate, ModifiedDate) AS 'Age', 
    65 - DATEDIFF (year, BirthDate, ModifiedDate) AS 'Years to Sentient' 
FROM 
    HumanResources.Employee

Of course, if the Employee is already over 65, then you will get a negative number, in which case, you might want to convert that negative number to a zero, for clarity, like so:

SELECT 
   BusinessEntityID, 
   DATEDIFF (year, BirthDate, ModifiedDate) AS 'Age', 
   (CASE 
       WHEN DATEDIFF (year, BirthDate, ModifiedDate) >= 65 
    THEN 
       0 
    ELSE 
       65 - DATEDIFF (year, BirthDate, ModifiedDate) 
    END) AS 'Years to Sentient' 
FROM 
    HumanResources.Employee