This page has 0 threads | Add post

Calculating Age with a User-Defined Function

Written by Andrew Gould

In this tutorial

Creating the Basic Structure of a Function
Declaring Variables
Calculating the Age and Returning the Result
Executing the Code
Using a User-Defined Function

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

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

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.

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

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

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.

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:

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.

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.

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!

Previous
tutorial

Calculations and Expressions

Previous
part

How to Calculate Age in SQL

Top of page

Next
part

Next
tutorial

Joins

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Dates and times

Calculating Age with a User-Defined Function

Creating the Basic Structure of a Function

Declaring Variables

Calculating the Age and Returning the Result

Executing the Code

Using a User-Defined Function

Head office

London

Manchester