2

I am using the DATEDIFF function in SQL Server 2012. I have two dates, 2015-01-01 and current_date. I need DATEDIFF in terms of Years , Months & Days between these two dates. below are my statements :

SELECT DATEDIFF(YY, '2015-01-01', GETDATE()) AS 'Years'
SELECT DATEDIFF(MM, '2015-01-01', GETDATE()) AS 'Months'
SELECT DATEDIFF(DD, '2015-01-01', GETDATE()) AS 'Days'

The result I am getting is: Years = 3, Months = 46, Days = 1416

Why in months & days it is adding an extra year?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sumeet Kumar
  • 321
  • 4
  • 17

3 Answers3

3

DATEDIFF() measures the number of time boundaries between two date/time values.

So, with year, it measures the number of times that the year flips (i.e. that new years start).

With month, it measures the number of times that the month flips (i.e. that new months start).

With day, it measures the number of times that the day flips (i.e. that new days start).

These are all independent of each other.

I would recommend that you not try to get the value in years/months/days. This is a hard problem. What is the number of months/days between Feb 28 and Mar 31? Between Jan 31 and Feb 28? Between Jan 31 and Mar 31? They don't add up, making this a hard problem.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
3

Perhaps a little overkill, but if you are open to a TVF

Example

Declare @YourTable table (SomeDate date)
Insert Into @YourTable values 
('2015-01-01')

Select A.SomeDate
      ,B.*
 From  @YourTable A
 Cross Apply [dbo].[tvf-Date-Elapsed](SomeDate,GetDate()) B

Returns

SomeDate    Years   Months  Days    Hours   Minutes Seconds
2015-01-01  3       10      16      8       22      40

The Function if Interested

CREATE FUNCTION [dbo].[tvf-Date-Elapsed] (@D1 DateTime,@D2 DateTime)
Returns Table
Return (
    with cteBN(N)   as (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
         cteRN(R)   as (Select Row_Number() Over (Order By (Select NULL))-1 From cteBN a,cteBN b,cteBN c),
         cteYY(N,D) as (Select Max(R),Max(DateAdd(YY,R,@D1))From cteRN R Where DateAdd(YY,R,@D1)<=@D2),
         cteMM(N,D) as (Select Max(R),Max(DateAdd(MM,R,D))  From (Select Top 12 R From cteRN Order By 1) R, cteYY P Where DateAdd(MM,R,D)<=@D2),
         cteDD(N,D) as (Select Max(R),Max(DateAdd(DD,R,D))  From (Select Top 31 R From cteRN Order By 1) R, cteMM P Where DateAdd(DD,R,D)<=@D2),
         cteHH(N,D) as (Select Max(R),Max(DateAdd(HH,R,D))  From (Select Top 24 R From cteRN Order By 1) R, cteDD P Where DateAdd(HH,R,D)<=@D2),
         cteMI(N,D) as (Select Max(R),Max(DateAdd(MI,R,D))  From (Select Top 60 R From cteRN Order By 1) R, cteHH P Where DateAdd(MI,R,D)<=@D2),
         cteSS(N,D) as (Select Max(R),Max(DateAdd(SS,R,D))  From (Select Top 60 R From cteRN Order By 1) R, cteMI P Where DateAdd(SS,R,D)<=@D2)

    Select [Years]   = cteYY.N
          ,[Months]  = cteMM.N
          ,[Days]    = cteDD.N
          ,[Hours]   = cteHH.N
          ,[Minutes] = cteMI.N
          ,[Seconds] = cteSS.N
          --,[Elapsed] = Format(cteYY.N,'0000')+':'+Format(cteMM.N,'00')+':'+Format(cteDD.N,'00')+' '+Format(cteHH.N,'00')+':'+Format(cteMI.N,'00')+':'+Format(cteSS.N,'00')
     From  cteYY,cteMM,cteDD,cteHH,cteMI,cteSS
)
--Max 1000 years
--Select * from [dbo].[tvf-Date-Elapsed] ('1991-09-12 21:00:00.000',GetDate())
--Select * from [dbo].[tvf-Date-Elapsed] ('2017-01-01 20:30:15','2018-02-05 22:58:35')
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • This works pretty well. One result this returns that I would disagree with would be between `20160229` and `20170328`; the return value is 1 year 1 month. Personally I would suggest that it's 1 year 28 days (as the value of days is lower than the start), however, leap years are always "fun" trouble makers. :) – Thom A Nov 17 '18 at 14:33
  • @Larnu In your scenario, you're spanning a leap year, so it is 1 month. If you were to try 20160229 - 20160328 you would get 28 days. At least this is how I count. That said, I appreciate the challenge :) – John Cappelletti Nov 17 '18 at 14:44
  • I agree, `20160229`-`20160328` is 28 days, but that's why I also feel that `20160229` -`20170328` is 1 year 28 days (not 1 year 1 month). Like wise, I wouldn't say that `20160229`-`20170329` is 1 year 1 day, but 1 year 1 month. But, like I said, leap years are always "trouble makers". – Thom A Nov 17 '18 at 14:47
  • 1
    @larnu I think the only sensible criteria is that the results should be usable in `DATEADD()` to go from `@D1` to `@D2`. But, then, the order you add the intervals matters; `DATEADD(DAY, 29, DATEADD(YEAR, 1, '2015-01-31')) != DATEADD(YEAR, 1, DATEADD(DAY, 29, '2015-01-31'))` *(Adding the largest interval first makes sense to me)*. Equally, however, multiple legitimate answers are possible; `DATEADD(DAY, 28, DATEADD(YEAR, 1, '2016-02-29')) == DATEADD(MONTH, 1, DATEADD(YEAR, 1, '2016-02-29'))` – MatBailie Nov 17 '18 at 15:14
  • @larnu (and John) : comments on this attempt? https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=56649374de812a6e01ec736f73f5ec40 – MatBailie Nov 17 '18 at 16:14
  • @MatBailie Impressive, but when I test 2016-02-29 - 2017-03-28 I get 11 Months and 58 days. Other than that my TVF matches your results (sans the milliseconds) https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=c2f9b7a90ebc79c66ad0d367bbcb4d41 – John Cappelletti Nov 17 '18 at 16:35
  • @JohnCappelletti The difference is that in some cases the TVF here gives the same interval between two dates even when adding 1 more day to the "start date" *(because adding a month to `2018-01-28` gives the same result as adding a month to `2018-01-31`)*, but the linked method doesn't *(See rows 3, 4, 5 of the link in this comment)*. As for the mistake, I've corrected that to make one adjustment or the other, but not both, thanks :) https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=6c4a647b91f031692a3b348cca1c397f – MatBailie Nov 17 '18 at 16:56
  • @MatBailie Here is a better illustration. You will see how each bucket decreases. https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=f88afb37edffd3dc5ceda06b43fc9e55 – John Cappelletti Nov 17 '18 at 18:13
  • This one shows a few more cases where neighbouring input rows give the same result as each other. https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=2680edd3eb2b805b1ab0864a905d18ca – MatBailie Nov 17 '18 at 19:09
  • @MatBailie I see what you are saying now. Going revisit the logic. Thanks – John Cappelletti Nov 17 '18 at 19:28
1

My take on @JohnCappelletti's inspired answer

CREATE FUNCTION [dbo].[tvf_interval_parts] (@start DateTime, @cease DateTime)
Returns Table
RETURN
  SELECT
    *
  FROM
  (
    SELECT
      months / 12   AS years,
      months % 12   AS months
    FROM
    (
      SELECT
        CASE WHEN DATEADD(MONTH, months, @start) > @cease                           THEN months - 1
             WHEN DATEADD(MONTH, months, @start) = DATEADD(MONTH, months, @start-1) THEN months - 1
                                                                                    ELSE months     END   AS months
      FROM
      (
        SELECT DATEDIFF(MONTH, @start, @cease) AS months
      )
        provisional
    )
      adjusted
  )
    interim
  CROSS APPLY
  (
      SELECT
        milliseconds                         / (24 * 60 * 60 * 1000)  AS days,
        milliseconds % (24 * 60 * 60 * 1000) / (     60 * 60 * 1000)  AS hours,
        milliseconds % (     60 * 60 * 1000) / (          60 * 1000)  AS minutes,
        milliseconds % (          60 * 1000) / (               1000)  AS seconds,
        milliseconds % (               1000)                          AS milliseconds
      FROM
      (
        SELECT DATEDIFF_BIG(millisecond, DATEADD(MONTH, 12 * years + months, @start), @cease) AS milliseconds
      )
        provisional
  )
    remainder

Example usage here : https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=47b2d437c4cea78d182ce0f63772ef38

MatBailie
  • 83,401
  • 18
  • 103
  • 137