SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Avoiding Unexpected Results with DATEDIFF in Year Comparisons

By David Strack,

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
Hopefully this will help in your date reporting and please feel free to leave any comments on your experience with date comparisons in SQL Server. 
 
Total article views: 1338 | Views in the last 30 days: 16
 
Related Articles
FORUM

Difference between Datediff('DD') & Datediff('D')

Difference between Datediff('DD') & Datediff('D')

FORUM

DATEDIFF WEEKS!!!

DATEDIFF WEEKS !!!

FORUM

DateDiff Problem

Problem with DateDiff

BLOG

DATEDIFF vs. DATEADD

Let’s talk about the DATEDIFF vs. DATEADD functions in SQL Server. Each one performs a different tas...

FORUM

datetime difference and display in years

datetime deifference

Tags
date    
datediff    
sql server    
t-sql    
 
Contribute