Calculating Age in SQL with a User-Defined Function
A common requirement in SQL queries is calculating a person's age. The expression you need to do this accurately is relatively long, so why not replace it with a user-defined function? This blog shows you how!

This blog is part of our complete SQL Tutorial. For training with real trainers, have a look at our SQL training courses.

Posted by Andrew Gould on 16 February 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Calculating Age with a User-Defined Function

I've written about how to calculate age in SQL Server in a previous blog and explained the pros and cons of three different methods.  The final, and most accurate, method is shown below for reference:

Combining DATEDIFF and DATEADD

The most accurate way to calculate age in SQL Server.

The expression is quite difficult to read, but it is the most accurate way to calculate an age in years in SQL Server.  The most annoying thing about this method is having to type out the enormous expression each time you want to use it.  A neat solution for avoiding finger-ache is to create a user-defined function that you can then call upon each time you need to calculate age.

This article isn't meant to be a comprehensive tutorial on creating user-defined functions in SQL Server; for that you'll want to look up the relevant article on our SQL Tutorial page.

Creating the Basic Structure of a Function

The first thing we'll do is create the basic function structure.  Open a new query window and type in (or copy and paste) the following:

-- Tell SQL Server to create a function called AGE

-- which accepts a single parameter which must be

-- of the DATETIME data type

CREATE FUNCTION AGE(@DateOfBirth AS DATETIME)

--The function returns an integer

RETURNS INT

AS

BEGIN

--Code to perform the calculation goes here

END

If you want to specify a database in which to create the function you can add a USE statement to the top of the code.  If you do this make sure you add the GO command immediately afterwards, as shown below.

Using GO

Without the GO command your code will not work.

Declaring Variables

While we don't need variables to successfully create our function it will make the code easier to read (and explain!) if we do use them.  Edit your code to include the following variable declarations:

CREATE FUNCTION AGE(@DateOfBirth AS DATETIME)

RETURNS INT

AS

BEGIN

--The result of DATEDIFF

DECLARE @Years AS INT

--The result of DATEADD

DECLARE @BirthdayDate AS DATETIME

--The actual age

DECLARE @Age AS INT

END

Calculating the Age and Returning the Result

With variables we can break the calculation down into separate steps to make it easier to read.  Edit your code so that it looks like this:

CREATE FUNCTION AGE(@DateOfBirth AS DATETIME)

RETURNS INT

AS

BEGIN

DECLARE @Years AS INT

DECLARE @BirthdayDate AS DATETIME

DECLARE @Age AS INT

--Calculate difference in years

SET @Years = DATEDIFF(YY,@DateOfBirth,GETDATE())

--Add years to DateOfBirth

SET @BirthdayDate = DATEADD(YY,@Years,@DateOfBirth)

--Subtract a year if birthday is after today

SET @Age = @Years -

CASE

WHEN @BirthdayDate>GETDATE() THEN 1

ELSE 0

END

--Return the result

RETURN @Age

END

Executing the Code

You can run this code in the same way that you would run a query: press F5 on the keyboard, or click the Execute button on the toolbar.

The end result

If you've done everything correctly you should see this text in the Messages window.

If you'd like to see your function you might need to refresh the Object Explorer pane first, as shown below:

Refreshing the Object Explorer

Select your database in the list and then click the Refresh button at the top of the pane.

 

You should now be able to find your function in the relevant folder within your database.

Seeing your function

You'll have to dig quite deeply into the structure of your database to find your function.

 

Using a User-Defined Function

Using a user-defined function in a query is exactly the same as using a built-in function, except for one thing: you must precede the name of your function with the schema name.

Using a function

Make sure to add the name of the schema (here it is dbo.) to the front of your function, otherwise it won't work.

 

The great news is that you'll never have to type out the long expression to calculate age ever again!

This blog has 0 threads Add post