How do you calculate someone’s age in years, months and days?

In the Microsoft SQL Server Group on LinkedIn (http://lnkd.in/qYvG5R), a question was asked:

How do you calculate someone’s age in years, months and days.

I gave it a shot and… well… failed.

Here’s my updated code. It will take care of cases where the birth date is Feb 29th or any birthdate on the 29th-31st of a given month. (this is where I failed last time).

DECLARE @birthday      [date]   = CAST('1972-02-29' AS [date]);

WITH 
	CTE_10 AS
		(	SELECT CAST(1 as [int]) as [value] UNION ALL
			SELECT CAST(1 as [int]) as [value] UNION ALL
			SELECT CAST(1 as [int]) as [value] UNION ALL
			SELECT CAST(1 as [int]) as [value] UNION ALL
			SELECT CAST(1 as [int]) as [value] UNION ALL
			SELECT CAST(1 as [int]) as [value] UNION ALL
			SELECT CAST(1 as [int]) as [value] UNION ALL
			SELECT CAST(1 as [int]) as [value] UNION ALL
			SELECT CAST(1 as [int]) as [value] UNION ALL
			SELECT CAST(1 as [int]) as [value]
		),
	CTE_1000000 AS
		(	SELECT	[01].[value]
			FROM	[CTE_10] [01], 
					[CTE_10] [02], 
					[CTE_10] [03], 
					[CTE_10] [04], 
					[CTE_10] [05], 
					[CTE_10] [06] 
		), 
	CTE_Numbers AS
		(	SELECT	ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as [value]
			FROM	CTE_1000000
		),
	CTE_Dates AS
		(	SELECT	@birthday													as [birthday_full], 
					DAY		(	@birthday									)	as [birthday_day], 
					DATEADD	(	day, [c].[value], @birthday					)	as [date_full], 
					MONTH	(	DATEADD(day, [c].[value], @birthday)		)	as [date_month], 
					DAY		(	DATEADD(day, [c].[value], @birthday)		)	as [date_day], 
					MONTH	(	DATEADD(day, [c].[value] + 1, @birthday)	)	as [next_date_month] 
			FROM	CTE_Numbers [c]
			WHERE	DATEADD(day, [c].[value], @birthday) <= GETDATE()
		), 
	CTE_Celebrations AS
		(	SELECT	[d].[date_full], 
						CASE	-- in February, any birthdays on the 29, 30, 31 are celebrated on the last day of Febrary
							WHEN	[d].[date_month]		= 2							AND 
									[d].[birthday_day]		IN (29, 30, 31)					AND 
									[d].[date_month] + 1	= [d].[next_date_month]
									THEN 1
							-- in any month with 30 days, any birthdays on the 31 are celebrated on the last day of the month (30th)
							WHEN	[d].[date_month]		IN (4, 6, 9, 11)			AND 
									[d].[birthday_day]		= 31						AND 
									[d].[date_month] + 1	= [d].[next_date_month]
									THEN 1
							-- general case
							WHEN	[d].[date_day]			= [d].[birthday_day]
									THEN 1
					END	as [monthly_birthday]
			FROM	CTE_Dates [d]
		)
SELECT	CAST(GETDATE() as [date])																														as [todays_date], 
		@birthday																																		as [birth_date], 
		SUM([d].[monthly_birthday]) / 12																												as [years],
		SUM([d].[monthly_birthday]) % 12																												as [months], 
		-- find last birthday/month, count days since
		DATEDIFF(day, (SELECT MAX([c].[date_full]) [last_monthly_birthday] FROM CTE_Celebrations [c] WHERE [monthly_birthday] IS NOT NULL), GETDATE())	as [days]
FROM	CTE_Celebrations [d]
WHERE	[d].[monthly_birthday] IS NOT NULL

Let’s break the solution down piece by piece

CTE_10

We’re going to build up a Tally Table. The starting point is CTE_10 is a Common Table Expression that delivers a single column result set with 10 row. All rows contain the value 1, which is of type [int]

CTE_1000000

Continuing in the grand tradition of CTE_10, we’re going to do a Cartesian product of CTE_10 six times, yielding 1,000,000 rows, all values equal 1

CTE_Numbers

CTE_Numbers turns 1M rows where all values equal 1 to a result set with 1M rows… except now every row will have a unique value starting with 1 and ending with 1M. This is our Tally table.

CTE_Dates

This hasn’t been very exciting yet. Now it is going to start to get interesting. Now that I have my Tally table, we are going to use it to generate all of the dates from the supplied birth date through the current day. This is the function of the column [date_full].

But as long as we’re here, we’re going to present the relevant dates, plus their components:

[birthday_full] [birthday_day]
[date_full] [date_month] [date_day]
[next_date_month]

We’re going to use these values in order to calculate the number of months between the supplied birth date and the current date.

Why months? What happened to years? We’ll, in the Gregorian calendar, there are always 12 months. This isn’t the case in the Hebrew/Jewish calendar, where there can be leap months. Just one more reason to kvetch about being a Jew.

If we can figure the number of months, divide by 12 and we have the number of years. The remainder or modulo tells us the number of months. Two birds, one stone.

Thanks to Paul D. Hunter for pointing out this.

CTE_Celebrations

Now for the work.

How do we go about figuring out if a given date [date_full] is the monthly anniversary of a given birth date?

Well, at first pass, it seems easy. If the day portion of the date [date_day] equals the day portion of the birthday [birthday_day], you have a match.

This is where I messed up.

Let’s say that someone has a birthday on the 31st of the month. Well, there isn’t a 31st of February, so that’s going to be missed.

Let’s take a look at our cases:

Case 1: Birth date occurs on the 29th, 30th or 31st, but the date we are testing is in Feb.
Case 2: Birth date occurs the 31st, but the date we are testing is in April, June, September or November (they only have 30 days)

Case 1

If the date we’re testing is in February, but the [birthday_day] is 29, 30 or 31, ask yourself:
On what date is that person 1 month old?

My answer: on the last day of February.

How do you figure the last day of February? It’s when the [date_month] + 1 = [next_day_month]

In a year without a leap year, that’s going to be 28 Feb ([date_month] = 2). The next day will have [next_day_month] = 3.

In a year with a leap year, that’s going to be 29 Feb ([date_month] = 2). The next day will have [next_day_month] = 3.

So that’s our test:

WHEN	[d].[date_month]		= 2							AND 
		[d].[birthday_day]		IN (29, 30, 31)				AND 
		[d].[date_month] + 1	= [d].[next_date_month]
		THEN 1

Case 2

The same pattern applies to birth dates on the 31st for months that only have 30 days.

WHEN	[d].[date_month]		IN (4, 6, 9, 11)			AND 
		[d].[birthday_day]		= 31						AND 
		[d].[date_month] + 1	= [d].[next_date_month]
		THEN 1

Case 2

The general case (if there is no match to the conditions above) is if [date_day] = [birthday_day], then there is a match.

Dates that don’t match one of the three conditions above return NULL. This is useful later.

The Query

So what’s left to do?

Sum up the number of months divided by 12 gives you the number of years since a birth date.

The modulo gives you the number of months since the last birthday.

Now, we figure the last monthly birthday by getting the latest date where [monthly_birthday] IS NOT NULL. This eliminates most of the rows, so it’s pretty efficient. Then we get the number of days since that date.

And…. we’re done.

Advertisements
  1. Leave a comment

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

%d bloggers like this: