Wise Owl's Blog
Making Sense of Software
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.
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:
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.
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.
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!
Comments on this blog
This blog currently has no comments.