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                                                             `