Red Devil - 4 months ago 8
SQL Question

# need to calculate year,month and day for closing date

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

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
THEN DATEDIFF(YEAR,Opendate,Closedate) - 1
ELSE DATEDIFF(YEAR,Opendate,Closedate)
END Years
FROM D
), YDate as
(
FROM Y
),M AS
(
SELECT Opendate,Closedate,Years,Newopendate,
THEN DATEDIFF(MONTH,Newopendate,Closedate) - 1
ELSE DATEDIFF(MONTH,Newopendate,Closedate)
END Months
FROM YDate
)
``````Opendate    Closedate   Years   Months  days