need to calculate year,month and day for closing date

Question

I have a table called dates,

Opendate    |   Closedate   
------------+---------------
2015-07-09  |   2016-08-10  

I am expecting the output like,

opendate    |   closedate   |   diff    
------------+---------------+----------------------
2015-07-09  |   2016-08-10  |   1year 1month 1day   
2015-07-09  |   2016-03-01  |   8 months 20 days
2015-07-09  |   2015-07-11  |   2 days

But when I run this query:

SELECT opendate, 
       closedate, 
       Datediff(year, opendate, closedate)  AS years, 
       Datediff(month, opendate, closedate) AS months, 
       Datediff(day, opendate, closedate)   AS days 
FROM   dates 

It is giving me an output like,

opendate    |   closedate   | years | months |  days    
------------+---------------+-------+--------+---------
2015-07-09  |   2016-08-10  |   1   |   13   |  397 

How can we calculate 1 year 1 month and 1 day

Answer 1

You can use Stacked CTE to find one by one the next year, month and date.

Explanation

Query Below first finds out the DATEDIFF Years of opendate and closedate and checks if the resulting date is greater than closedate. if it is, the actual year difference is DATEDIFF of Y -1. use this new date and fetch the DATEDIFF of months using the same logic and then get the difference in days.

Online Example

Query

WITH D(Opendate,Closedate)AS
(
SELECT CAST('2015-07-09' AS DATE),CAST('2016-08-10' AS DATE)
UNION ALL 
SELECT CAST('2015-07-09' AS DATE),CAST('2016-03-01' AS DATE)
UNION ALL 
SELECT CAST('2015-07-09' AS DATE),CAST('2015-07-11' AS DATE)

),Y AS
(
SELECT Opendate,Closedate,
  CASE 
  WHEN DATEADD(YEAR,DATEDIFF(YEAR,Opendate,Closedate),Opendate) > Closedate 
  THEN DATEDIFF(YEAR,Opendate,Closedate) - 1 
  ELSE DATEDIFF(YEAR,Opendate,Closedate)
  END Years
FROM D
), YDate as 
(
SELECT Opendate,Closedate,Years,DATEADD(YEAR,Years,Opendate) as Newopendate
FROM Y
),M AS
(
SELECT Opendate,Closedate,Years,Newopendate,
CASE WHEN DATEADD(MONTH,DATEDIFF(MONTH,Newopendate,Closedate),Newopendate) > Closedate 
THEN DATEDIFF(MONTH,Newopendate,Closedate) - 1 
ELSE DATEDIFF(MONTH,Newopendate,Closedate) 
END Months
FROM YDate
) 
SELECT Opendate,Closedate,Years,Months,DATEDIFF(Day,DATEADD(MONTH,Months,Newopendate),Closedate) as days
FROM M

Result

Opendate    Closedate   Years   Months  days
09-07-2015 00:00    10-08-2016 00:00    1   1   1
09-07-2015 00:00    01-03-2016 00:00    0   7   21
09-07-2015 00:00    11-07-2015 00:00    0   0   2