Michael Valentine Jones (7/20/2009)

---

The logic for Age in this select statment shows a simple way to do it.

declare @date_of_birth datetime

set @date_of_birth = '19770801'

select

AGE =

-- Find difference in years and subtract 1 if date is before this years birthday

datediff(yy,@date_of_birth, a.date) -

case when dateadd(yy,datediff(yy, @date_of_birth, a.DATE),@date_of_birth) > a.DATE

then 1 else 0 end,

DATE_OF_BIRTH = @date_of_birth,

a.DATE

from

-- Test data from Date Table Function F_TABLE_DATE

-- Function code available here:

-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

F_TABLE_DATE('20090725','20090805') a

order by

a.DATE

Results:

AGE DATE_OF_BIRTH DATE

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

31 1977-08-01 00:00:00.000 2009-07-25 00:00:00.000

31 1977-08-01 00:00:00.000 2009-07-26 00:00:00.000

31 1977-08-01 00:00:00.000 2009-07-27 00:00:00.000

31 1977-08-01 00:00:00.000 2009-07-28 00:00:00.000

31 1977-08-01 00:00:00.000 2009-07-29 00:00:00.000

31 1977-08-01 00:00:00.000 2009-07-30 00:00:00.000

31 1977-08-01 00:00:00.000 2009-07-31 00:00:00.000

32 1977-08-01 00:00:00.000 2009-08-01 00:00:00.000

32 1977-08-01 00:00:00.000 2009-08-02 00:00:00.000

32 1977-08-01 00:00:00.000 2009-08-03 00:00:00.000

32 1977-08-01 00:00:00.000 2009-08-04 00:00:00.000

32 1977-08-01 00:00:00.000 2009-08-05 00:00:00.000

(12 row(s) affected)