SQL – quickly calculate age in years

Here is a quick way to calculate someone’s age using T-SQL.
DECLARE @BirthDate DATETIME;
SET @Birthdate='20110225'

SELECT
(
CAST(CONVERT(CHAR(8), GETDATE(), 112) AS INT)
- CAST(CONVERT(CHAR(8), @BirthDate, 112) AS INT)
)/10000

In case you haven’t tried to do it on your own, beleive me, it is not trivial to calculate the age, given the quirks of leap years, months with varying number of days etc. But this gives you the right result every time!

Impressive, eh! Well, before you profusely thank me, I have to mention that I stole this from one of Itzik Ben-Gan’s book. If I could come up with something like this on my own, I would not be wasting time writing in my regurgitated blog now would I?

All kidding aside, Itzik’s books are awesome!! Try one and you will be hooked.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s