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