Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

DZone's Guide to

# Calculate Age On Given Date In SQL

· ·
Free Resource

Comment (0)

Save
{{ articles[0].views | formatCount}} Views
```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
``````
Topics:

Comment (0)

Save
{{ articles[0].views | formatCount}} Views

Opinions expressed by DZone contributors are their own.