Implementing YEARDIFF

Question: How do I compute the number of complete years between two timestamps?

Answer: Call DATEDIFF YEAR and if you don't like the answer, subtract 1!

That's not as silly as it sounds. DATEDIFF has two huge advantages over a start-from-scratch-and-do-it-yourself approach:

• DATEDIFF takes care of those pesky leap years and the funky divisible-by-4-vs-100-vs-400 rule (2000 and 2400 are leap years, while 1800, 1900, 2100, 2200, 2300 and 2500 are NOT leap years), and
  

• DATEDIFF works... it's already been tested by bazillions of developers and end users.
  

DATEDIFF's problem is that it computes the number of year boundaries between two timestamps, not the number of complete years. The two numbers are equal for exactly half of all possible pairs of timestamps, and DATEDIFF's answer is too high by 1 for the other half.

But wait!

There's another function you can call to determine if DATEDIFF got it right or not: DATEADD.

Yes, DATEADD... take the number of years returned by DATEDIFF and call DATEADD to add it to the first timestamp (assuming the first timestamp is the smaller one). If the result is less than or equal to the second timestamp, then DATEDIFF returned the right number. If not, then DATEADD's answer is too high by 1... hence the "subtract 1" in the answer above.

Here's the code, followed by a test using timestamps that are different by only one microsecond, as well as one year boundary:

CREATE FUNCTION YEARDIFF ( IN @timestamp1 TIMESTAMP, IN @timestamp2 TIMESTAMP ) RETURNS SMALLINT DETERMINISTIC BEGIN DECLARE @lesser_timestamp TIMESTAMP; DECLARE @greater_timestamp TIMESTAMP; DECLARE @sign SMALLINT; DECLARE @yeardiff SMALLINT; IF @timestamp1 IS NULL OR @timestamp1 IS NULL THEN RETURN NULL; END IF; IF @timestamp1 = @timestamp2 THEN RETURN 0; END IF; IF @timestamp1 < @timestamp2 THEN SET @sign = 1; SET @lesser_timestamp = @timestamp1; SET @greater_timestamp = @timestamp2; ELSE SET @sign = -1; SET @lesser_timestamp = @timestamp2; SET @greater_timestamp = @timestamp1; END IF; SET @yeardiff = DATEDIFF ( YEAR, @lesser_timestamp, @greater_timestamp ); IF DATEADD ( YEAR, @yeardiff, @lesser_timestamp ) > @greater_timestamp THEN RETURN @sign * ( @yeardiff - 1 ); ELSE RETURN @sign * @yeardiff; END IF; END; SELECT DATEDIFF ( YEAR, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS DATEDIFF_YEAR_1, YEARDIFF ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS YEARDIFF_1, DATEDIFF ( YEAR, '2012-01-01 00:00:00.0000000', '2011-12-31 23:59:59.9999999' ) AS DATEDIFF_YEAR_2, YEARDIFF ( '2012-01-01 00:00:00.0000000', '2011-12-31 23:59:59.9999999' ) AS YEARDIFF_2; DATEDIFF_YEAR_1 YEARDIFF_1 DATEDIFF_YEAR_2 YEARDIFF_2 --------------- ---------- --------------- ---------- 1 0 -1 0 SELECT DATEDIFF ( YEAR, '2010-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS DATEDIFF_YEAR_3, YEARDIFF ( '2010-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS YEARDIFF_3, DATEDIFF ( YEAR, '2012-01-01 00:00:00.0000000', '2010-12-31 23:59:59.9999999' ) AS DATEDIFF_YEAR_4, YEARDIFF ( '2012-01-01 00:00:00.0000000', '2010-12-31 23:59:59.9999999' ) AS YEARDIFF_4; DATEDIFF_YEAR_3 YEARDIFF_3 DATEDIFF_YEAR_4 YEARDIFF_4 --------------- ---------- --------------- ---------- 2 1 -2 -1

Here's another test that how a straightforward call to DATEDIFF flubs a simple age calculation but gets it right when YEARDIFF handles the call to DATEDIFF:

SELECT DATEDIFF ( YEAR, '2012-07-27', '2013-07-28' ) AS correct_age, DATEDIFF ( YEAR, '2012-07-29', '2013-07-28' ) AS incorrect_age; correct_age incorrect_age ----------- ------------- 1 1 SELECT YEARDIFF ( '2012-07-27', '2013-07-28' ) AS correct_age_1, YEARDIFF ( '2012-07-29', '2013-07-28' ) AS correct_age_2; correct_age_1 correct_age_2 ------------- ------------- 1 0

---