# Performing a Right and Comprehensive Age Calculation

• Comments posted to this topic are about the item Performing a Right and Comprehensive Age Calculation

_____________
Code for TallyGenerator

• Great job, much appreciated!. Although, a small correction to your script.
In the first part, with @BirthDate and @Today declared as DATETIME, the TallyGenerator for years should be called starting from zero.
This way we will get a correct age calculation for small infants as well π (age below 1 year won't be returned the way it's written now).

• I too have struggled with this in the past.  The easiest and fastest way I have found to do this is by using the function below.  It works every time, even for leap years and is only 1 line of code!

CREATE FUNCTION [dbo].[fn_Age_Calc] (
@DOB DATETIME
,@AsAtDate DATETIME
)
RETURNS INT
AS
BEGIN
--Get the birthday for the year of the As At Date.
--It basically works by converting the date into a number
--in yyyymmdd format and that way there is a difference of
--exactly 10000 between the same date of two different years.
--It even works with leap years.
DECLARE @Age INT

SET @Age = (0+Convert(Char(8),@AsAtDate,112) - Convert(Char(8),@DOB,112)) / 10000

RETURN @Age
END

• Why do you not use a much more short and simple
`DECLARE @bd DATE = '19801231', @TD DATE = '20171231'SELECT DATEDIFF(YEAR, @bd, @TD) - CASE WHEN DATEPART(MONTH, @bd) * 100 + DATEPART(day, @bd)               > DATEPART(MONTH, @TD) * 100 + DATEPART(day, @TD)             THEN 1              ELSE 0             END `
to calculate the age?

Its the same as I do in real life - get the year diffence between today and birthdate and and subtract one more when he / she had not yet birthday in this year.

God is real, unless declared integer.

• Alex Burlan - Tuesday, March 28, 2017 3:33 AM

Great job, much appreciated!. Although, a small correction to your script.
In the first part, with @BirthDate and @Today declared as DATETIME, the TallyGenerator for years should be called starting from zero.
This way we will get a correct age calculation for small infants as well π (age below 1 year won't be returned the way it's written now).

Thanks.
Well spotted.
Funny enough, it's zero in the scripts for year and month calculations, and then I've got that "1" in the final script from somewhere.

Must be a protection against mindless copy-paste users.
π

_____________
Code for TallyGenerator

• t.franz - Tuesday, March 28, 2017 3:58 AM

Why do you not use a much more short and simple
`DECLARE @bd DATE = '19801231', @TD DATE = '20171231'SELECT DATEDIFF(YEAR, @bd, @TD) - CASE WHEN DATEPART(MONTH, @bd) * 100 + DATEPART(day, @bd)               > DATEPART(MONTH, @TD) * 100 + DATEPART(day, @TD)             THEN 1              ELSE 0             END `
to calculate the age?

Its the same as I do in real life - get the year diffence between today and birthdate and and subtract one more when he / she had not yet birthday in this year.

One of the obvious reasons "why" - lack of months and days of the age in the output.

_____________
Code for TallyGenerator

• Sergiy - Tuesday, March 28, 2017 4:18 AM

Alex Burlan - Tuesday, March 28, 2017 3:33 AM

Great job, much appreciated!. Although, a small correction to your script.
In the first part, with @BirthDate and @Today declared as DATETIME, the TallyGenerator for years should be called starting from zero.
This way we will get a correct age calculation for small infants as well π (age below 1 year won't be returned the way it's written now).

Thanks.
Well spotted.
Funny enough, it's zero in the scripts for year and month calculations, and then I've got that "1" in the final script from somewhere.

Must be a protection against mindless copy-paste users.
π

I updated the attached script, published version should be refreshed some time soon.

_____________
Code for TallyGenerator

• The first query in the script doesn't return any data for the following inputs

`DECLARE @BirthDate datetime, @Today DATETIME select @BirthDate='1976-02-29', @Today='2017-03-28'`

`DECLARE @BirthDate datetime, @Today DATETIME select @BirthDate='1976-02-28', @Today='2017-03-28'`

• /*

Assuming that today is 2017-03-16 (YYYY-MM-DD),

then the Days value is incorrect for DOB's between2016-01-17 and 2016-01-30.

Days = 16 for 2016-01-28, 2016-01-29, 2016-01-30 and2016-01-31.

*/

DECLARE @Today DATETIME2 = GETDATE()-12

;WITH DOBs AS (

SELECTDOB = DATEADD(DAY,1-ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),CAST(@Today ASDATETIME2))

FROM

(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d1 (n),

(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d2 (n),

(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d3 (n),

(VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d4 (n),

(VALUES(0),(0),(0),(0),(0)) d5 (n)

)

SELECT d.DOB, Today = @Today, [AgeInYears],[AgeInMonths], [AgeInDays], z.*

FROM DOBs d

CROSS APPLY (

SELECT

[AgeInDays]= CASE

WHENDAY(@Today) >= DAY(d.DOB) THEN DAY(@Today) - DAY(d.DOB)

ELSEDAY(@Today) + (DATEDIFF(DAY,d.DOB,EOMONTH(d.DOB)))

END,

[AgeInYears]= (0 + CONVERT(CHAR(8),@Today,112) - CONVERT(CHAR(8),d.DOB,112)) / 10000

) x1

CROSS APPLY (

) y

CROSS APPLY (

SELECTTOP 1 T3.Years, T3.Months, N [Days]

--             , DATEADD(dd, N, T3.DateFrom)DateFrom, DateTo

FROMdbo.TallyGenerator(1,31, NULL, 1) tg2

INNERJOIN (

SELECTTOP 1 T2.Years, tg2.N Months, DATEADD(mm, N, T2.EndOfLastWholeYear)EndOfLastWholeMonth, T2.DateTo

FROMdbo.TallyGenerator(1,12, NULL, 1) tg2

INNERJOIN (

SELECTTOP 1 tg1.N Years, DATEADD(YY, N, T1.DateFrom) EndOfLastWholeYear, T1.DateTo

FROMdbo.TallyGenerator(1,DATEDIFF(YY, d.DOB, @Today)+1, NULL, 1) tg1

INNERJOIN (

SELECTd.DOB DateFrom, @Today DateTo

)T1 ON T1.DateTo >= DATEADD(YY, N, T1.DateFrom)

ORDERBY N DESC

)T2 ON T2.DateTo >= DATEADD(mm, N, T2.EndOfLastWholeYear)

ORDERBY N DESC

)T3 ON T3.DateTo >= DATEADD(dd, N, T3.EndOfLastWholeMonth)

ORDERBY N DESC

) z

-- 44,299 / 00:00:01

[/code]

βWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.β - Gail Shaw

Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• kevriley - Tuesday, March 28, 2017 4:56 AM

The first query in the script doesn't return any data for the following inputs

`DECLARE @BirthDate datetime, @Today DATETIME select @BirthDate='1976-02-29', @Today='2017-03-28'`

`DECLARE @BirthDate datetime, @Today DATETIME select @BirthDate='1976-02-28', @Today='2017-03-28'`

I saw the same thing..
select @BirthDate='1968-03-22', @Today='2017-03-28'

• DATEDIFF(HOUR,DOB,GETDATE())/8766

• you are missing the point.. the problem is not to know elapsed hours (or days or seconds) - it is to know years, months, days (and hours) correctly

• andersen.bo - Tuesday, March 28, 2017 8:06 AM

kevriley - Tuesday, March 28, 2017 4:56 AM

The first query in the script doesn't return any data for the following inputs

`DECLARE @BirthDate datetime, @Today DATETIME select @BirthDate='1976-02-29', @Today='2017-03-28'`

`DECLARE @BirthDate datetime, @Today DATETIME select @BirthDate='1976-02-28', @Today='2017-03-28'`

I saw the same thing..
select @BirthDate='1968-03-22', @Today='2017-03-28'

Not sure I can understand what you are guys are talking about
DECLARE @BirthDate datetime, @Today DATETIME
select @BirthDate='2003-07-31', @Today='2008-07-30'
select @BirthDate='1976-02-29', @Today='2017-03-28'
select @BirthDate='1976-02-28', @Today='2017-03-28'
select @BirthDate='1968-03-22', @Today='2017-03-28'
SELECT TOP 1 T3.Years, T3.Months, N Days
--    , DATEADD(dd, N, T3.DateFrom) DateFrom, DateTo
FROM dbo.TallyGenerator(0, 31, NULL, 1) tg2
INNER JOIN (
SELECT TOP 1 T2.Years, tg2.N Months, DATEADD(mm, N, T2.EndOfLastWholeYear) EndOfLastWholeMonth, T2.DateTo
FROM dbo.TallyGenerator(0, 12, NULL, 1) tg2
INNER JOIN (
SELECT TOP 1 tg1.N Years, DATEADD(YY, N, T1.DateFrom) EndOfLastWholeYear, T1.DateTo
FROM dbo.TallyGenerator(0, DATEDIFF(YY, @BirthDate, @Today)+1, NULL, 1) tg1
INNER JOIN (
SELECT @BirthDate DateFrom, @Today DateTo
) T1 ON T1.DateTo >= DATEADD(YY, N, T1.DateFrom)
ORDER BY N DESC
) T2 ON T2.DateTo >= DATEADD(mm, N, T2.EndOfLastWholeYear)
ORDER BY N DESC
) T3 ON T3.DateTo >= DATEADD(dd, N, T3.EndOfLastWholeMonth)
ORDER BY N DESC
go

Works fine.

Could it be because of that silly typo in TallyGenerator calls?
FROM dbo.TallyGenerator(10, ...

If yes - sorry about it.
I fixed the attached script already, but  the update did not make it to the site just yet.

_____________
Code for TallyGenerator

• andersen.bo - Tuesday, March 28, 2017 3:21 PM

you are missing the point.. the problem is not to know elapsed hours (or days or seconds) - it is to know years, months, days (and hours) correctly

I've been calculating age using...DATEDIFF(HOUR,DOB,GETDATE())/8766...for a great many years, and it's never been wrong yet!

• m2c2 - Tuesday, March 28, 2017 3:49 PM

andersen.bo - Tuesday, March 28, 2017 3:21 PM

you are missing the point.. the problem is not to know elapsed hours (or days or seconds) - it is to know years, months, days (and hours) correctly

I've been calculating age using...DATEDIFF(HOUR,DOB,GETDATE())/8766...for a great many years, and it's never been wrong yet!

Then you're lucky not to have a 5-year-old born on 3/28/2012, because they'll be the first to scream at you that DATEDIFF(HOUR,'2012-03-28','2017-03-28')/8766 is horribly wrong, and no, they're not 4 anymore, now they're a big 5 year old!

That being said, if you're required to report elapsed time in years, months, and days and you report it in years, you're still wrong no matter how accurate your answer may be.

Viewing 15 posts - 1 through 15 (of 31 total)