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