One interesting task you may be given as a data professional is to calculate a person’s age based on their date of birth.
In this brief tutorial, we’ll discuss a simple script you can use to derive the number of years, months, and days that a person has aged since their date of birth!
The DATEDIFF system function
The script we’ll use to derive a person’s age will use the DATEDIFF system function heavily.
The syntax for this function looks like this:
DATEDIFF(<datepart>, <starting-date>, <ending-date>)
Let’s walk through each part of this syntax:
- datepart: This is a keyword representing what kind of date interval you want use in the calculation. For example, if you want to know how many years are between 7/28/1988 and 3/31/2024, you would specify a datepart of “year“. There are many datepart values you can specify (week, day, month, hour, second, etc.).
- start-date: This will be the starting date value you want to use in the calculation.
- end-date: This will be the ending date value you want to use in the calculation.
If we wanted to know the difference in years between 7/28/1988 and 3/31/2024, for example, the statement would look like this:
SELECT DATEDIFF(YEAR, '7/28/1988', '3/31/2024') AS DifferenceInYears
Here is what the output looks like:
You could use the handy GETDATE() system function as the end-date value. In that case, it would calculate the number of years between the starting date and the present date (I’m actually writing this on 3/31/2024):
A person born on 7/28/1988 isn’t quite 36 yet (as of the time of this writing)
At the time of this writing (3/31/2024), it would be inaccurate to say a person born in July of ’88 is 36 years old. That person will be 36 this year, but they aren’t that old yet.
That is exactly why the task of calculating a person’s age can be tricky. When determining a person’s age, you aren’t just looking at the difference in years. You also need to consider the difference in months and days.
Through our script, we’ll be able to accurately derive a person’s true age!
The script:
Now that we understand the task, I’ll show you the script we can use:
DECLARE @dateOfBirth DATE DECLARE @dateBuffer DATE DECLARE @numYears INT DECLARE @numMonths INT DECLARE @numDays INT SET @dateOfBirth = '7/28/88' SET @dateBuffer = @dateOfBirth --Calculate the number of years that have gone by between the person's birth year and the present year --Subtract 1 if we haven't gotten to the person's birthday yet this year. SELECT @numYears = DATEDIFF(yy, @dateBuffer, GETDATE()) - CASE WHEN (MONTH(@dateOfBirth) > MONTH(GETDATE())) OR (MONTH(@dateOfBirth) = MONTH(GETDATE()) AND DAY(@dateOfBirth) > DAY(GETDATE())) THEN 1 ELSE 0 END SELECT @dateBuffer = DATEADD(yy, @numYears, @dateBuffer) --@dateBuffer becomes 7/28/2023 --Calculate the number of months that have gone by between the person's *last* birthday and the present date --Subtract 1 if we haven't gotten to the person's birth 'day' yet this month SELECT @numMonths = DATEDIFF(m, @dateBuffer, GETDATE()) - CASE WHEN DAY(@dateOfBirth) > DAY(GETDATE()) THEN 1 ELSE 0 END SELECT @dateBuffer = DATEADD(m, @numMonths, @dateBuffer) --@dateBuffer becomes 3/28/2024 --Calculate how many days have gone by since the person's birth 'day' value this month. SELECT @numDays = DATEDIFF(d, @dateBuffer, GETDATE()) SELECT @numYears as 'Years', @numMonths as 'Months', @numDays as 'Days'
(Feel free to change the @dateOfBirth value to your birthday for testing!)
We’ll walk through this script bit by bit. First, this:
SET @dateBuffer = @dateOfBirth
We’re going to change the value of the @dateBuffer variable throughout this script. The first thing we do is set it to the person’s birth date.
Then this:
--Calculate the number of years that have gone by between the person's birth year and the present year --Subtract 1 if we haven't gotten to the person's birthday yet this year. SELECT @numYears = DATEDIFF(yy, @dateBuffer, GETDATE()) - CASE WHEN (MONTH(@dateOfBirth) > MONTH(GETDATE())) OR (MONTH(@dateOfBirth) = MONTH(GETDATE()) AND DAY(@dateOfBirth) > DAY(GETDATE())) THEN 1 ELSE 0 END SELECT @dateBuffer = DATEADD(yy, @numYears, @dateBuffer) --@dateBuffer becomes 7/28/2023
This code starts off by using DATEDIFF to calculate the number of years between the birth year and this year.
The important thing to notice in this code is that we subtract 1 if we haven’t yet made it to the person’s birthday this year.
Once we have adequately set our @numYears value, we need to change our buffer using the handy DATEADD function. What we do is add the @numYears value we calculated to the original birth year value. For example, in our case, that brings the birth year from 1988 to 2023.
The next block of code does a similar process:
--Calculate the number of months that have gone by between the person's *last* birthday and the present date --Subtract 1 if we haven't gotten to the person's birth 'day' yet this month SELECT @numMonths = DATEDIFF(m, @dateBuffer, GETDATE()) - CASE WHEN DAY(@dateOfBirth) > DAY(GETDATE()) THEN 1 ELSE 0 END SELECT @dateBuffer = DATEADD(m, @numMonths, @dateBuffer) --@dateBuffer becomes 3/28/2024
Once again, we use DATEDIFF to calculate the difference in months between our new buffer date and the present date.
The buffer date, at this point, is 7/28/2023. The difference in months between July of ’23 and March of ’24 is 8 months.
If we haven’t quite gotten to the person’s birth ‘day‘ this month, we need to subtract 1. That’s not the case in our testing example.
After that, it’s time to reset the buffer date again. This time, we add the number of months we’ve calculated (8) to our buffer date. That brings the buffer date to a new value of 3/28/2024.
Finally, this:
--Calculate how many days have gone by since the person's birth 'day' value this month. SELECT @numDays = DATEDIFF(d, @dateBuffer, GETDATE())
This last part simply calculates the difference in days between the buffer date and the present date. In our case, that is simply 3 days.
Here is what the output looks like. Once again, we’re dealing with a birthday of 7/28/88 and a present date of 3/31/2024:
Those values look correct!
You should think about saving this code as a stored procedure or a function
If you will be in a position of needing to calculate a person’s age frequently, it would be a great idea to save this code as an object in the database. You could possibly save it as a stored procedure or a function.
Here’s an example of creating the code as a stored procedure:
CREATE OR ALTER PROCEDURE CalculateAge @dateOfBirth DATE AS DECLARE @dateBuffer DATE DECLARE @numYears INT DECLARE @numMonths INT DECLARE @numDays INT SET @dateBuffer = @dateOfBirth SELECT @numYears = DATEDIFF(yy, @dateBuffer, GETDATE()) - CASE WHEN (MONTH(@dateOfBirth) > MONTH(GETDATE())) OR (MONTH(@dateOfBirth) = MONTH(GETDATE()) AND DAY(@dateOfBirth) > DAY(GETDATE())) THEN 1 ELSE 0 END SELECT @dateBuffer = DATEADD(yy, @numYears, @dateBuffer) SELECT @numMonths = DATEDIFF(m, @dateBuffer, GETDATE()) - CASE WHEN DAY(@dateOfBirth) > DAY(GETDATE()) THEN 1 ELSE 0 END SELECT @dateBuffer = DATEADD(m, @numMonths, @dateBuffer) SELECT @numDays = DATEDIFF(d, @dateBuffer, GETDATE()) SELECT @numYears as 'Years', @numMonths as 'Months', @numDays as 'Days' GO
Notice we have an input parameter called @dateOfBirth. The idea with this procedure is that the caller will pass in a birth date value and the procedure will use that to calculate a person’s age. The result will be a simple result set showing the age of a person in years, months, and days. Here’s an example:
So much easier!
Maybe we could create a scalar function to tell us if it is presently someone’s birthday. Here’s an example of a list of Customers and their birthdays:
It would be great if we could create a scalar function that could be ran every day to tell us whose birthday is that day.
Again, the time of this writing is 3/31/2024. Looking at the data set, it looks like Andrew Bluefield and Gretchen Hamilton have birthdays today! Maybe it would be great to send them an email wishing them a happy birthday.
We can create a scalar function that would examine each person’s date of birth and tell us if that person has a birthday today. Here’s what the code looks like:
CREATE OR ALTER FUNCTION dbo.IsItTheirBirthday(@dateOfBirth DATE) RETURNS BIT AS BEGIN DECLARE @isItTheirBirthday BIT DECLARE @dateBuffer DATE DECLARE @numYears INT DECLARE @numMonths INT DECLARE @numDays INT SET @dateBuffer = @dateOfBirth SELECT @numYears = DATEDIFF(yy, @dateBuffer, GETDATE()) - CASE WHEN (MONTH(@dateOfBirth) > MONTH(GETDATE())) OR (MONTH(@dateOfBirth) = MONTH(GETDATE()) AND DAY(@dateOfBirth) > DAY(GETDATE())) THEN 1 ELSE 0 END SELECT @dateBuffer = DATEADD(yy, @numYears, @dateBuffer) SELECT @numMonths = DATEDIFF(m, @dateBuffer, GETDATE()) - CASE WHEN DAY(@dateOfBirth) > DAY(GETDATE()) THEN 1 ELSE 0 END SELECT @dateBuffer = DATEADD(m, @numMonths, @dateBuffer) SELECT @numDays = DATEDIFF(d, @dateBuffer, GETDATE()) IF(@numMonths = 0 AND @numDays = 0) SET @isItTheirBirthday = 1 ELSE SET @isItTheirBirthday = 0 RETURN @isItTheirBirthday END GO
Notice the IF…ELSE block at the end. If we think about it, if the @numMonths and @numDays values both end up being 0, it must be the person’s birthday today! In that case, we set our BIT variable to 1. Otherwise, we set it to 0.
We could call this function in a simple query:
So easy! Again, we could run exactly this same code every day and it will simply return to us the people who have birthdays on that day!
You could make this even easier by saving the query above as a View:
Then we simply need to query the View to find out who has a birthday today:
Isn’t SQL fun???
Next Steps:
Leave a comment if you found this tutorial helpful!
The DATEDIFF and DATEADD system functions are very useful. Take a look at the full beginner-friendly tutorials to learn all about how they work:
SQL Server DATEDIFF System Function: Explained
SQL Server DATEADD System Function: Everything you need to know
Full credit for the content of this post needs to be given to the following Stack Overflow article:
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
