CODEWITHSUNDEEP
sqlsql-serversql-server-2008date
Red Devil
Red Devil

Reputation: 2393

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

Upvotes: 3

Views: 342

Answers (3)

Chetan Sanghani
Chetan Sanghani

Reputation: 2111

Create one function as Below

 CREATE FUNCTION dbo.GetYearMonthDays
    (
       @FromDate DATETIME
    )
    RETURNS NVARCHAR(100)
    AS
    BEGIN
       DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
       SELECT @date =@FromDate

    SELECT @tmpdate = @date

    SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
    SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
    SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
    SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
    SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

        RETURN  CONVERT(varchar(10), @years) +' Years   ' +  CONVERT(varchar(10), @months)  + ' Month  ' + CONVERT(varchar(10), @days) + ' Days'
    END
    GO

And use is as below

SELECT opendate, 
       closedate,dbo.GetYearMonthDays(closedate)
FROM   dates

This will give you what you wants.

Upvotes: 1

ughai
ughai

Reputation: 9880

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

Upvotes: 4

Tharsan Sivakumar
Tharsan Sivakumar

Reputation: 6531

SELECT opendate, 
       closedate, 
       ( ( Datediff(year, opendate, closedate) +  'years' )+  
       (( Datediff(month, opendate, closedate) - 
             12 * Datediff(year, opendate, closedate)) + 'months') +
        ( Datediff(day, opendate, closedate) - 
         ( Datediff(year, opendate, closedate) * 365 - 
         (Datediff(month, opendate, closedate) * 12) )) + 'days'

FROM   dates

The logic is you concatenate the years and then deduct the no of months of a year. Similarly deduct for days as well

Upvotes: 1

Related Questions