How to get the difference in years between two dates in SQL?

Getting down to the day: Leap years and birthdays

When dealing with leap years or counting down to exact birthdays or anniversaries, the details matter. This use of (DATE_FORMAT(EndDate, '%m%d') < DATE_FORMAT(StartDate, '%m%d')) adjusts the difference by one, accommodating for instances when the end date hasn't yet reached the start date's day and month for the given year.

Why do it yourself? Use built-in functions

MySQL offers the TIMESTAMPDIFF function to simplify the task:

-- The function TIMESTAMPDIFF does all the heavy lifting here
SELECT TIMESTAMPDIFF(YEAR, StartDate, EndDate) AS YearDiff
FROM YourTable


Alternatively, you can use FLOOR and DATEDIFF for complete year differences:

-- Working out (pun intended), the difference using "floor" and "datediff"
SELECT FLOOR(DATEDIFF(EndDate, StartDate) / 365) AS YearDiff
FROM YourTable


Put these queries to the test using SQLFiddle or a similar online SQL environment.

When round and positive matters

If you require rounded numbers and/or positive differences, combine ABS and TO_DAYS:

-- Round the result so it's the same both ways
SELECT
  ROUND(ABS(TO_DAYS(EndDate) - TO_DAYS(StartDate)) / 365) AS RoundedYearDiff
FROM YourTable


For more functions, take a look at the official MySQL documentation.

Edge cases and best practices for performance

Always remember edge cases:

• Dates falling within the same year
• Exact matching dates for birthdays
• Implementation of DATEDIFF when the end date is a leap day

For performance, index your date columns if you're crunching large datasets. Indexing can drastically shrink the time it takes for your queries to run by sidestepping the need for full table scans.

Check and verify your calculations

Before you roll out your queries, test them on a range of test cases:

-- Key advice: Always wear a helmet when testing!
SELECT
  CASE
    WHEN YEAR(EndDate) = YEAR(StartDate) THEN 'Same Year'
    WHEN DATE_FORMAT(EndDate, '%m%d') = '0229' AND MONTH(StartDate) > 2 THEN 'Leap Day Adjustment'
    ELSE 'Multi-Year'
  END AS CaseType,
  YourCalculation AS YearDiff
FROM YourTable


Think of this as your helpful SQL bicycle helmet, safeguarding you by checking for special cases while selecting the year difference based on your unique calculation logic.