declare @birthday datetime, @d datetime
select @birthday = '12/31/1972', @d = '1/1/99'
select datediff(yy, @birthday, @d) -
        (case when (datepart(m, @birthday) > datepart(m, @d)) or
                        (datepart(m, @birthday) = datepart(m, @d) And
                                datepart(d, @birthday) > datepart(d, @d))
                        then 1
                        else 0
        end) as Age1

-- Just for completion, another way to do the same.
-- I am not sure if this will work for all dates but
-- this is an easy approximate way.
select year ( dateadd( dd, datediff ( dd , @birthday , @d ) ,
                '1900-01-01 00:00' ) )  - 1900

-- A general question:
-- To find out if a year is leap year or not, use
if ( year ( @d ) % 400 = 0 ) Or
        ( year( @d ) % 4 = 0 And year ( @d ) % 100 <> 0 )
        print 'Leap Year - 366 days'
else
        print 'Not a leap year - 365.25 days'

This page was last updated on May 01, 2006 04:28 PM.