Age Calculations

In DATETIME Calculations, Part 5, June 2007, InstantDoc #95675, I
covered Age Calculations among other topics. I presented the following
technique to calculate the age of a person given a birth date and an event
date:

DECLARE
  @birthdate AS DATETIME,
  @eventdate AS DATETIME;
 
SET @birthdate = '20040229';
SET @eventdate = '20070228'; -- also test '20070227', '20070301'
 
SELECT
  DATEDIFF(year, @birthdate, @eventdate)
  - CASE WHEN 100 * MONTH(@eventdate) + DAY(@eventdate)
            
Save one special case, the expected result is the same for all systems. The


special case is when someone was born on February 29th in a leap year, 

and the event year is a common (non-leap) year; more specifically, the event 

date is February 28th in a common year. 

The technique I presented in the article is adequate for systems that expect 

the age to change on March 1st in a common year. I got feedback from 

Gustav Brock from Denmark saying that the rule used by the public 

authorities and insurance companies in Denmark is that in this special case, a


person’s age changes on February 28th. Reality is that different systems 

(countries, and even within countries, different entities) may apply different


rules. The important thing is that you should verify which rule should be 

applied in the system at hand, and apply the relevant logical calculation 

accordingly. If in the special case a person’s age changes on March 1st, use 

the above technique. Otherwise, the calculation is actually simpler since 

SQL Server’s default behavior when you use the DATEADD function to 

add whole years to February 29th in a leap year, is to produce February 

28th in a common year. Here’s the calculation you can use in such a case:
DECLARE
  @birthdate AS DATETIME,
  @eventdate AS DATETIME;
 
SET @birthdate = '20040229';
SET @eventdate = '20070227'; -- also test '20070227', '20070301'
 
SELECT
  DATEDIFF(year, @birthdate, @eventdate)
  - CASE WHEN
      DATEADD(year, DATEDIFF(year, @birthdate, @eventdate),
              @birthdate) > @eventdate
         THEN 1 ELSE 0
    END AS Age;
 
-- Output 2 for @eventdate = '20070227'
-- Output 3 for @eventdate = '20070228'
-- Output 3 for @eventdate = '20070301'

Back to systems that use the rule that dictates that the age change on March


1 in the special case, Craig Pessano (online user name craigpessano) posted 

a beautiful technique that deserves kudos. The technique is amazingly simple 

and relies on integer division:
DECLARE
  @birthdate AS DATETIME,
  @eventdate AS DATETIME;
 
SET @birthdate = '20040229';
SET @eventdate = '20070227'; -- also test '20070227', '20070301'
 
SELECT
  (CAST(CONVERT(CHAR(8),@eventdate,112) AS INT)
   - CAST(CONVERT(CHAR(8),@birthdate,112) AS INT)) / 10000 AS Age;

-- Output 2 for @eventdate = '20070227'
-- Output 2 for @eventdate = '20070228'
-- Output 3 for @eventdate = '20070301'

The idea is to produce the integer representation of both dates in the form


YYYYMMDD; subtract the integer representing the birth date from the 

integer representing the event date, and divide the result by 10000 using 

integer division truncating the fraction.


Cheers,

--

BG