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)
           
<p>Save one special case, the expected result is the same for all systems. The
<br>
special case is when someone was born on February 29th in a leap year, <br>
and the event year is a common (non-leap) year; more specifically, the event <br>
date is February 28th in a common year. <br>
The technique I presented in the article is adequate for systems that expect <br>
the age to change on March 1st in a common year. I got feedback from <br>
Gustav Brock from Denmark saying that the rule used by the public <br>
authorities and insurance companies in Denmark is that in this special case, a
<br>
person’s age changes on February 28th. Reality is that different systems <br>
(countries, and even within countries, different entities) may apply different
<br>
rules. The important thing is that you should verify which rule should be <br>
applied in the system at hand, and apply the relevant logical calculation <br>
accordingly. If in the special case a person’s age changes on March 1st, use <br>
the above technique. Otherwise, the calculation is actually simpler since <br>
SQL Server’s default behavior when you use the DATEADD function to <br>
add whole years to February 29th in a leap year, is to produce February <br>
28th in a common year. Here’s the calculation you can use in such a case:</p>
<pre>
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