DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Jason has posted 22 posts at DZone. View Full User Profile

Calculate Age On Given Date In SQL

12.14.2009
| 3271 views |
  • submit to reddit
        SQL function to calculate the age in fully passed calendar years: it is the diff in years then subtract a year if birthday hasn't occurred yet. This means 29 Feb birthdays are observed on 1 Mar in non-leap years.

create function dbo.age_in_years
(
  @birth_date  datetime
  , @eval_date datetime
)
returns int
as
begin

  /*
  - Calculate the age in fully passed calendar years: it is the diff in years then
    subtract a year if birthday hasn't occurred yet. This means 29 Feb birthdays 
    are observed on 1 Mar on non-leap years.
  - Returns null if either argument is null or @eval_date < @birth_date.
  - Returns 0 until age 1.
  */
  return 
    case
      when @birth_date is null or @eval_date is null then null
      when @birth_date > @eval_date then null
      when @birth_date = @eval_date then 0
      else year(@eval_date) - year(@birth_date)
        - case /* make dates MMDD vs MMDD to check if the eval date is before the birth date*/
            when ((month(@eval_date) * 100) + day(@eval_date)) < ((month(@birth_date) * 100) + day(@birth_date)) then 1
            else 0
          end 
    end

/*
declare @AgeInYearsTests table ( birth_date datetime null, eval_date datetime null, expected_age int null )
insert into @AgeInYearsTests values ('2000-02-29', '2000-02-28', null)
insert into @AgeInYearsTests values ('2000-02-29', '2000-02-29', 0)
insert into @AgeInYearsTests values ('2000-02-29', '2000-03-01', 0)
insert into @AgeInYearsTests values ('2000-02-29', '2001-02-27', 0)
insert into @AgeInYearsTests values ('2000-02-29', '2001-02-28', 0)
insert into @AgeInYearsTests values ('2000-02-29', '2001-03-01', 1)
insert into @AgeInYearsTests values ('2000-02-29', '2002-02-27', 1)
insert into @AgeInYearsTests values ('2000-02-29', '2002-02-28', 1)
insert into @AgeInYearsTests values ('2000-02-29', '2002-03-01', 2)
insert into @AgeInYearsTests values ('2000-02-29', '2003-02-27', 2)
insert into @AgeInYearsTests values ('2000-02-29', '2003-02-28', 2)
insert into @AgeInYearsTests values ('2000-02-29', '2003-03-01', 3)
insert into @AgeInYearsTests values ('2000-02-29', '2004-02-28', 3)
insert into @AgeInYearsTests values ('2000-02-29', '2004-02-29', 4)
insert into @AgeInYearsTests values ('2000-02-29', '2004-03-01', 4)
insert into @AgeInYearsTests values ('2000-02-29', '2005-02-27', 4)
insert into @AgeInYearsTests values ('2000-02-29', '2005-02-28', 4)
insert into @AgeInYearsTests values ('2000-02-29', '2005-03-01', 5)
insert into @AgeInYearsTests values ('2000-02-29', '2017-02-27', 16)
insert into @AgeInYearsTests values ('2000-02-29', '2017-02-28', 16)
insert into @AgeInYearsTests values ('2000-02-29', '2017-03-01', 17)
insert into @AgeInYearsTests values ('2000-02-29', '2018-02-27', 17)
insert into @AgeInYearsTests values ('2000-02-29', '2018-02-28', 17)
insert into @AgeInYearsTests values ('2000-02-29', '2018-03-01', 18)
insert into @AgeInYearsTests values ('2000-02-29', '2055-02-27', 54)
insert into @AgeInYearsTests values ('2000-02-29', '2055-02-28', 54)
insert into @AgeInYearsTests values ('2000-02-29', '2055-03-01', 55)
insert into @AgeInYearsTests values ('2000-12-30', '2018-12-29', 17)
insert into @AgeInYearsTests values ('2000-12-30', '2018-12-30', 18)
insert into @AgeInYearsTests values ('2000-12-30', '2018-12-31', 18)
insert into @AgeInYearsTests values ('2000-12-30', '2019-01-01', 18)
insert into @AgeInYearsTests values ('2000-02-29', null, null)
insert into @AgeInYearsTests values (null, '2000-02-28', null)
insert into @AgeInYearsTests values (null, null, null)

select *, dbo.age_in_years(birth_date, eval_date) as actual
from @AgeInYearsTests
where expected_age <> dbo.age_in_years(birth_date, eval_date)
*/

end