Calculate Age (in years)

,

An iTVF that calculates the age between two dates in years. Runs much faster as an inline table valued function than as a scalar valued function by about 3-4 times based on my testing. 

See Comments in the code for more details. 

CREATE FUNCTION dbo.YearsApart(@FromDate DATETIME, @ToDate DATETIME) 
/****************************************************************************************
Purpose:
 Determines the time between two dates in years. Handles leap years. 

Examples:
--====== Basic use, two variables: 
 SELECT age 
 FROM dbo.YearsApart('1/1/2000','12/31/2015');

--====== Against a table (from somedate through today): 
 WITH sampledata as
 (
   SELECT id, somedate = CAST(somedate AS date)
	FROM (VALUES (1,'1/1/2000'),(2,'10/1/2002'), (3,'1/23/2010')) t(id, somedate)
  )
  SELECT id, somedate, today = CAST(getdate() AS date), age
  FROM sampledata
  CROSS APPLY dbo.YearsApart(somedate,getdate());

Developer Notes:
 1. Based on the algorithm in this article: 
    http://www.sqlteam.com/article/datediff-function-demystified

 2. This is an iTVF (Inline Table Valued Function) that performs the same task as a 
    scalar user defined function (UDF) accept that it requires the APPLY table operator. 
    Note the usage examples below and See this article for more details: 
	http://www.sqlservercentral.com/articles/T-SQL/91724/ 
	
	The function will be slightly more complicated to use than a scalar UDF but will yeild
	much better performance. For example - unlike a scalar UDF, this function does not 
	restrict the query optimizer's ability generate a parallel query plan. Initial testing
	showed that the function generally gets a 

 3. Performs approximately 3-4 times faster than a scalar UDF version of this function 
    using the same algorithm. 

---------------------------------------------------------------------------------------
Revision History:
 Rev 00 - 20070320 - Initial development - Peter Larsson
 Rev 01 - 20150713 - Redesigned as iTVF  - Alan Burstein
****************************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS RETURN 
SELECT age = 
  CASE 
    WHEN @FromDate > @ToDate THEN NULL
    WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) 
      THEN DATEDIFF(month, @FromDate, @ToDate) - 1
    ELSE DATEDIFF(month, @FromDate, @ToDate) 
  END / 12	
GO

Rate

4.63 (8)

Share

Share

Rate

4.63 (8)