Getting The Age of an Person in SQL Server Query Using UDF DateDiff Does Not Work by Les Smith

Need to determine the age of a person in a SQL Server Query?  DateDiff will not work!  Build a UDF that works.

I recently needed a SQL Server Query to be able to determine the age of a person in order to limit the rows returned by the Stored Procedure.  I needed the query to reject persons that were less than 21 years old.  At first I was using DateDiff and found, after careful investigation that I was gettng rows back that were supposed people who were at least 21 years old, but were in fact within a few months of being 21.

The basic problem is that the DateDiff function in SQL Server works like the DateDiff function in VB.  If you run Reflector on the DateDiff function in VB.NET, you will find out that the DateDiff function is accurate only when the interval is "Day".  When you attempt to use it for a "Month" or "Year" interval, it may get close, depending on the month's involved in the interval, or whether there is a Leap Year involved, etc.   But, as we all know, "Close counts in Darts, Horseshoes, and Nuclear War", but not when trying to determine if a person has reached the age of 21!

The reason for this is the simplistic algorithm that DateDiff uses.  For example, when the interval is Year, it only takes the difference in whole years.  That may be ok for some simple calculation, but it will not work for determing the age of a person.  In the application that I am working, the query has legal implications if the person is not yet 21 years old.

Well, it turns out that SQL Server DateDiff is no more sophisticated than it's VB counterpart.  At first I was a little shocked, but then I began to panic wondering how many times and for what purpose I had used DateDiff in the past.  After dismissing that thought, I had to build a workable solution, and I needed it in a SQL Server Query, otherwise I would have to retrieve many more rows than was practical and then wade through the DataSet to cast out the persons under 21.

The solution became obvious; build a user-defined function (UDF) in SQL Server and call it from the Stored Procedure.  The code shown next is the source of the UDF.

Create FUNCTION dbo.GetAge (@DOB datetime, @Today Datetime) RETURNS Int AS Begin Declare @Age As Int Set @Age = Year(@Today) - Year(@DOB) If Month(@Today) < Month(@DOB) Set @Age = @Age -1 If Month(@Today) = Month(@DOB) and Day(@Today) < Day(@DOB) Set @Age = @Age - 1 Return @AGE End

Next I modified my Stored Procedure, which had originally had the DateDiff in it, to call the UDF that accurately returns the age of the person.  The main code of the Stored Procedure is shown next.  It will not return persons that are less than 21 years of age.

SELECT Last_Name, First_Name, ssn, dob FROM Employee_Data e (nolock) WHERE Cust_Id = 'Customer1' and dbo.GetAge(e.Date_Of_Birth, getdate()) >= 21

If you have any comments or suggestions on this article or any other programming subject you would like to discuss, comment on my blog at Click Here.