March 27, 2017 at 10:37 pm
Comments posted to this topic are about the item Performing a Right and Comprehensive Age Calculation
_____________
Code for TallyGenerator
March 28, 2017 at 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).
Thank you for this article.
March 28, 2017 at 3:48 am
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
March 28, 2017 at 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.
God is real, unless declared integer.
March 28, 2017 at 4:18 am
Alex Burlan - Tuesday, March 28, 2017 3:33 AMGreat 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).Thank you for this article.
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
March 28, 2017 at 4:21 am
t.franz - Tuesday, March 28, 2017 3:58 AMWhy 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
March 28, 2017 at 4:41 am
Sergiy - Tuesday, March 28, 2017 4:18 AMAlex Burlan - Tuesday, March 28, 2017 3:33 AMGreat 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).Thank you for this article.
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
March 28, 2017 at 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'
March 28, 2017 at 7:23 am
/*
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 (
SELECT[AgeInMonths] =DATEDIFF(month,DATEADD(YEAR,[AgeInYears],DATEADD(day,[AgeInDays],d.DOB)),@Today)
) 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]
For fast, accurate and documented assistance in answering your questions, please read this article.
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
March 28, 2017 at 8:06 am
kevriley - Tuesday, March 28, 2017 4:56 AMThe 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'
March 28, 2017 at 3:05 pm
DATEDIFF(HOUR,DOB,GETDATE())/8766
March 28, 2017 at 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
March 28, 2017 at 3:34 pm
andersen.bo - Tuesday, March 28, 2017 8:06 AMkevriley - Tuesday, March 28, 2017 4:56 AMThe 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
March 28, 2017 at 3:49 pm
andersen.bo - Tuesday, March 28, 2017 3:21 PMyou 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!
March 28, 2017 at 4:43 pm
m2c2 - Tuesday, March 28, 2017 3:49 PMandersen.bo - Tuesday, March 28, 2017 3:21 PMyou 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) correctlyI'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)
You must be logged in to reply to this topic. Login to reply