The key to finding age is to find the birthday for the current year, and subtract 1 from the difference in years if the current date is before the birthday this year.

Note that the code below computes the current year birthday for Feb 29 birthdays as Feb 28 for non-leap years and Feb 29 for leap years.

The concept of negative age doesn't seem to have any real world meaning, so my method returns null if the date of birth is before current date.

select

a.DOB,

a.CURR_DATE,

Age =

datediff(yy,a.DOB,a.CURR_DATE) +

case

-- Age is null when DOB before current date

when datediff(dd,a.DOB,a.CURR_DATE) < 0

then null

-- Subtract 1 if current date before birthday in current year

when datediff(dd,dateadd(yy,datediff(yy,a.DOB,a.CURR_DATE),a.DOB),a.CURR_DATE) < 0

then -1 else 0 end

from

( -- Test Data

select

DOB =convert(datetime,'20040407'),

CURR_DATE = convert(datetime,'20060203')

union all

select getdate(),dateadd(ms,100,getdate()) union all

select getdate(),dateadd(ms,-100,getdate()) union all

select getdate(),dateadd(dd,-1,getdate()) union all

select getdate(),dateadd(yy,10,dateadd(ms,-100,getdate())) union all

select getdate(),dateadd(yy,10,dateadd(ms,100,getdate())) union all

select '20040407','20040407' union all

select '20040407','20050406' union all

select '20040407','20050407' union all

select '20040229','20060227' union all

select '20040229','20060228' union all

select '20040229','20060301' union all

select '20040229','20080228' union all

select '20040229','20080229' union all

select '20060205','20060205' union all

select '17530101 00:00:00.000','99991231 23:59:59.997' union all

select '19500913', getdate()

) a

order by

a.DOB,

a.CURR_DATE

Results:

DOB CURR_DATE Age

----------------------- ----------------------- -----------

1753-01-01 00:00:00.000 9999-12-31 23:59:59.997 8246

1950-09-13 00:00:00.000 2013-01-08 16:52:54.810 62

2004-02-29 00:00:00.000 2006-02-27 00:00:00.000 1

2004-02-29 00:00:00.000 2006-02-28 00:00:00.000 2

2004-02-29 00:00:00.000 2006-03-01 00:00:00.000 2

2004-02-29 00:00:00.000 2008-02-28 00:00:00.000 3

2004-02-29 00:00:00.000 2008-02-29 00:00:00.000 4

2004-04-07 00:00:00.000 2004-04-07 00:00:00.000 0

2004-04-07 00:00:00.000 2005-04-06 00:00:00.000 0

2004-04-07 00:00:00.000 2005-04-07 00:00:00.000 1

2004-04-07 00:00:00.000 2006-02-03 00:00:00.000 1

2006-02-05 00:00:00.000 2006-02-05 00:00:00.000 0

2013-01-08 16:52:54.810 2013-01-07 16:52:54.810 NULL

2013-01-08 16:52:54.810 2013-01-08 16:52:54.710 0

2013-01-08 16:52:54.810 2013-01-08 16:52:54.910 0

2013-01-08 16:52:54.810 2023-01-08 16:52:54.710 10

2013-01-08 16:52:54.810 2023-01-08 16:52:54.910 10