The world’s leading publication for data science, AI, and ML professionals.

Using SQL’s DateDiff() for Age

A Potential Bug and How to Avoid It

Photo by Kortnee Greenfield via Unsplash
Photo by Kortnee Greenfield via Unsplash

At some point, you might be asked to pull customer data that includes the customer’s age at the time of their transaction/encounter with the company. Most likely, age at the time of transaction isn’t a column already in your data as it is dependent on when a certain event occurs.

The short solution is to use the built-in function DATEDIFF( ) where you are able to find the year difference between two dates. Let’s take a look at some results using this function. In these results, we will see today’s date that is compared to the birthday of the individuals.

Photo by Author
Photo by Author

Uh-oh! You just served alcohol to minors and now lightning is about to strike the business and burn it down to the ground. Rough, I know.

The Workaround

So, it looks like when using DATEDIFF( ), that the math is only really computed on the year value. Month and day of the year don’t appear to be factored into the equation. Let’s factor it in!

First, we will take the person’s birthday and will use the DATEADD( ) function. In this, we will add the number of years that we expect this person to turn in a given year, based on the DATEDIFF( ) results.

Finally, we will use a CASE statement. We can say that if the current event has occurred prior to or is occurring on the date the individual will have a birthday that year, then use the age produced by the DATEDIFF( ) function. If the individual’s birthday hasn’t occurred by the time the event takes place, take a year away from the expected age. This code, using T-Sql, is a little drawn out in order to show each step as it’s produced:

What the Results Look Like

Photo by Author
Photo by Author

Now, we see happy little green stars that will show us where age changes have occurred. No miscalculating ages on our watch!

Speaking of Birthdays

Have you ever wanted to wish a coworker a happy birthday, but in the most memorable way possible? Look no further. The code below, after you change the person’s name value, will actually return a nice little birthday message. You can always e-mail the code to the person or whatever works for you.

Final Thoughts

Spot checking results is crucial. I always like to use the question from a previous article, ‘What will break this?’, which I still love the idea of your imagination turning it into a gameshow crowd cheer.

Code is powerful. It speeds up what we are all able to do with data. Take the time to understand the results and how the tools work. As always, keep on learnin’.


Related Articles