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