KNOWLEDGE BASE

Calculating Difference in Dates Using DATEDIFF Returns Unexpected Results


Published: 12 May 2014
Last Modified Date: 13 Apr 2017

Issue

When using a DATEDIFF function to calculate the difference between two dates, the results might be incorrect.

Environment

Tableau Desktop

Resolution

Adjust the year by using the following calculation:
IF DATEPART('month',[Sart Date]) < DATEPART('month', [End Date]) THEN [Age (years)]
ELSEIF
((DATEPART('month',[Sart Date])) > (DATEPART('month', [End Date])))
THEN ([Age (years)] -1)
ELSEIF
((DATEPART('day',[Sart Date]) <= DATEPART('day',[End Date]))) THEN [Age (years)]
ELSE ([Age (years)]-1)
END

Cause

The DATEDIFF function is calculating based on complete year and not partial year. For example: Start date is June 2012 and end date is May 2013. The time in between the two dates above is not a complete year (only 11 months), however using DATEDIFF will state the difference is 1 for the datepart year.
Did this article resolve the issue?