SQL Server 2012 : DATEDIFF function in terms of years, days and months

user_7878102 0
Viewed 141

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?

date-difference datediff sql-server sql
3 Answers

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.

user_11440350

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')
user_15700000

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

Dems_533410
Related