Sometimes it is necessary to calculate a person's age.  I've seen people use a simple datediff on YEAR but that is not sufficient.  The reason why that is not sufficient is because it ONLY evaluates a difference on the YEAR value of the date.  So that means from December 31, 2008 to January 1, 2009 it would return 1.  Obviously that's not correct.  You have to also compensate for the day of the year. 

There are a couple of ways this can be done.  You can do inline SQL to calculate the value all the time, but the calculation takes a bit of code so it won't make reading it very easy.  I prefer to use a User Defined Function.

In this user defined function the first thing we do is determine the difference in years.  This is where most procedure I've seen stop.  The next thing we do is add the value, stored in @Age, to the @BirthDate variable.  This makes the year of the @BirthDate value the same year as the @AsOfDate.  This is important because now we can do a simple > calculation.  If @BirthDate hasn't come yet, then your age hasn't increased yet.

create function [dbo].[fn_GetAgeAsOf] (@BirthDate datetime, @AsOfDate datetime)
returns int
AS
begin
declare @Age   smallint
set @age=datediff(yy, @birthdate,@asofdate)
set @birthdate = dateadd(yy, @age, @birthdate)
if @birthdate>@AsOfDate
  set @Age=@Age-1
return @age
end
go

The function above will work, even when you are calculating age in a leap year.  The below function, shows how using a simple datepart(dy,@Birthdate) > datepart(dy,@AsOfDate) will not work.  The reason it won't work is the February 29 (during a leap year) and March 1 (during a non-leap year) both have the "Day of the Year" value of 60.  This will throw off the calculation for all dates after February.

WARNING!!! This is not the function we are suggesting to use.  It is merely to show you why it doesn't work.

create function [dbo].[fn_GetAgeAsOf3] (@BirthDate datetime, @AsOfDate datetime)
returns int
AS
begin
declare @Age   smallint
select @age=datediff(yy, @birthdate,@asofdate)
if datepart(dy,@birthDate)>datepart(dy,@AsOfDate)
  set @Age=@Age-1
return @age
end
go

Now let's run a couple of scripts with these two methods to show ow they work when spanning a leap year both with the leap year being selected as the date and March 1 being selected.

select [dbo].[fn_GetAgeAsOf3]('2007-03-01','2008-02-29'),[dbo].[fn_GetAgeAsOf]('2007-03-01','2008-02-29')
select [dbo].[fn_GetAgeAsOf3]('2007-03-01','2008-03-01'),[dbo].[fn_GetAgeAsOf]('2007-03-01','2008-03-01')

Below is the cleanup scripts for this exercise.

if object_id('[dbo].[fn_GetAgeAsOf]') is not null
     drop function [dbo].[fn_GetAgeAsOf]
if object_id('[dbo].[fn_GetAgeAsOf3]') is not null
     drop function [dbo].[fn_GetAgeAsOf3]

I hope that you find this useful and understand now how it can be a little more complicated than the simple solution when doing calculations in SQL Server.  To use the fn_AgeAsOf function to determine the current age, simply pass the current date as the @AsOfDate parameter.  Be mindful that if you simply pass in GETDATE() without stripping the time component, you can run into problems with the times being different and not doing a straight day calculation.