CODEWITHSUNDEEP
sqlsql-serverdatesubquerydifference
UserEsp
UserEsp

Reputation: 426

how to get the difference between 2 dates in years and months in same query using sub queries?

I'm using the Northwind DB SQL and trying to get the difference between 2 dates in years with months on it not separate. Example employee hireDate 1992-05-01 should be 27 years 0 months and 1992-08-14 must be 2 years 8 months using today as the date to compare. I want to get the results for each row of the table with other values in one result example

FirstName| hireDate | hiredYears | hiredMonths nancy 1992-05-01 6 11

This is my query

SELECT  months-(Age*12) FROM (

SELECT CASE WHEN dateadd(year, datediff (year, [HireDate], getdate()),[HireDate]) > getdate()
            THEN datediff(year, [HireDate], getdate()) - 1
            ELSE datediff(year, [HireDate], getdate())
       END as Age,

       (case when datepart(day,getdate()) >= datepart(day,[HireDate]) 
       then datediff(month, [HireDate], getdate())
       else datediff(month, [HireDate], dateadd(month,-1,getdate()))
       end) as months

FROM [Employees] 

) results;

The first query gave me the months and the subquery in years but I don't know how to create a subquery that shows everything together. Also, I will like to know if this subquery can have bad performance because I would like to use something similar in a table with a lot of records, any advice or link to read?

Upvotes: 0

Views: 398

Answers (3)

sticky bit
sticky bit

Reputation: 37472

I think you just need to divide the months by twelve to get the full years and use modulo twelve on them to get the remaining months. Use concat() to compose a string in the form of "n years m months".

SELECT concat(datediff(month, hiredate, getdate()) / 12,
              N' years ',
              datediff(month, hiredate, getdate()) % 12,
              N' months')
       FROM employees;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I think the right answer is to get the value in months and then do the arithmetic:

SELECT months / 12 as years,
       month % 12 as months
FROM employees e CROSS APPLY
     (VALUES (DATEDIFF(month, hiredate, getdate())
     ) v(months);

Upvotes: 2

mkRabbani
mkRabbani

Reputation: 16908

Try this-

SELECT FirstName, 
hireDate ,
DATEDIFF(YY,hireDate ,GETDATE()) hiredYears,
DATEDIFF(MM,hireDate ,GETDATE())%12 hiredMonths
FROM your_table

Upvotes: 1

Related Questions