UPDATE: The original version of this artcle contained an inaccuracy in how the date difference was determined. Please disregard the contents and use the following method.
One of the most common tasks in database reporting and development is determining the difference in years between two dates. This is often done using the DATEDIFF function and the YEAR argument. However, this can lead to unexpected results when the day in the date of the “startdate” argument falls after the day in the year of the “enddate” argument. In this scenario, DATEDIFF returns the difference of only the two years. For example, DATEDIFF returns a difference of eight years in the SQL below, however until October, 5th of this year is reached, the difference would actually be seven years.
DECLARE @STARTDATE DATETIME = '2010-10-05';
DECLARE @TODAY DATETIME = GETDATE();
SELECT DATEDIFF(YEAR, @STARTDATE, @TODAY);An option to avoid this issue is implenting a function as follows. This method checks whether initial date falls after the current day of the date given for the "endate" parameter, and if it does then one is subtracted from the final result. Is it important to note that this function would add additional overhead to a query, and careful consideration should be used if this calculation is necessary for a large volume of data.
CREATE FUNCTION [dbo].[udfDateDiffInYears] (@STARTDATE DATETIME)
RETURNS INT
AS
BEGIN
DECLARE @TODAY DATETIME = GETDATE(), @DIFFINYEARS INT
SET @DIFFINYEARS = DATEDIFF(YEAR, @STARTDATE, @TODAY) -
CASE WHEN @TODAY < DATEADD(YEAR, DATEDIFF(YEAR, @STARTDATE, @TODAY), @STARTDATE)
THEN 1 ELSE 0 END
RETURN @DIFFINYEARS
END