(Transact-SQL) DATEDIFF and leap years

Question

I have a problem with DATEDIFF function.

My date format is dd/mm/yyyy.

@START_DATE = 01/02/2004
@END_DATE = 29/01/2014

The query (DATEDIFF(DAY,@START_DATE,@END_DATE) / 365) return 10, but the number of correct years is 9. This happens because my query does not consider leap years.

What I can do to keep an accurate count? Thanks.

There are closer to 365.25 days in a year, so you shouldn't expect your arithmetic to be correct. — Gordon Linoff, Aug 29 '14 at 10:51

score 2 · Accepted Answer · answered Aug 29 '14 at 10:57

2

I believe the following logic does what you want:

   datediff(year,
            @START_DATE - datepart(dayofyear, @START_DATE) + 1,
            @END_DATE - datepart(dayofyear, @START_DATE) + 1
           ) as d2

Note: This treats that dates as datetime, because arithmetic is easier to express. You can also write this as:

   datediff(year,
            dateadd(day, - datepart(dayofyear, @START_DATE) + 1, @START_DATE),
            dateadd(day, - datepart(dayofyear, @START_DATE) + 1, @END_DATE)
           ) as d2

The following query is a demonstration:

select datediff(year,
                startdate - datepart(dayofyear, startdate) + 1,
                enddate - datepart(dayofyear, startdate) + 1
               ) as d2
from (select cast('2004-02-01' as datetime) as startdate,
             cast('2014-01-31' as datetime) as enddate
      union all
      select cast('2004-02-01' as datetime) as startdate,
             cast('2014-02-01' as datetime) as enddate

     ) t

answered Aug 29 '14 at 10:57

Gordon Linoff

1,242,037
58
646
786

It works! :D But I have one question: why adding +1? – Andryx93 Aug 29 '14 at 13:23
@Andryx93 . . . 2014-01-05 is the fifth day of the year. If you subtract 5, you get 2013-12-31. The idea is to normalize the dates to the beginning of the year for the comparison. – Gordon Linoff Aug 29 '14 at 13:35
year(@START_DATE - datepart(dayofyear, @START_DATE) + 1) is kind of useless. It will still be the same year as year(@START_DATE) as far as i can tell – t-clausen.dk Aug 29 '14 at 21:10
@t-clausen.dk . . . That is true. But I think about this logically where the time period is being shifted uniformly, so that start date is at the beginning of the year. I find that easier to follow than just shifting the end date. – Gordon Linoff Aug 29 '14 at 22:23

score 2 · Answer 2 · answered Jan 13 '15 at 16:41

Technically there would be 365.242 days in a year, when accounting for leap years so:

FLOOR(DATEDIFF(day, @STARTDATE, @ENDDATE) / 365.242)

Should be more correct.

Test:

SELECT  FLOOR(DATEDIFF(day, '1980-01-16','2015-01-15') / 365.242),
        FLOOR(DATEDIFF(day, '1980-01-16','2015-01-16') / 365.242)

ResultSet:

--------------------------------------- ---------------------------------------
34                                      35

Cheers!

score 1 · Answer 3 · answered Aug 29 '14 at 10:07

You can create a function to address that:

CREATE FUNCTION [dbo].[getYears] 
(
    @START_DATE datetime,
    @END_DATE datetime
)  
RETURNS int
AS  
BEGIN 
    DECLARE @yrs int
    SET @yrs =DATEDIFF(year,@START_DATE,@END_DATE)
    IF (@END_DATE < DATEADD(year, @yrs, @START_DATE)) 
       SET @yrs = @yrs -1
    RETURN @yrs
END

Also check this

score 0 · Answer 4 · answered Sep 17 '17 at 11:50

Count the number of leap days in end_date Deduct number of leap days in start_date. Deduct the answer from your DATEDIFF.

DECLARE
    @START_DATE DATETIME = '2004-02-01',
    @END_DATE DATETIME = '2014-01-29'

SELECT (
        DATEDIFF(DAY,@START_DATE,@END_DATE)
            - (
                (CONVERT(INT,@END_DATE - 58) / 1461) 
                -
                (CONVERT(INT,@START_DATE - 58) / 1461)
            )
        ) / 365

-58 to ignore Jan and Feb 1900

/ 1461 being the number of days between leap years

(Transact-SQL) DATEDIFF and leap years

4 Answers4

Linked