March 29, 2017 at 1:43 am
Sergiy - Tuesday, March 28, 2017 3:34 PMandersen.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
Assuming that today is 2017-03-16 (YYYY-MM-DD),
then the Days value is incorrect for DOB's between 2016-01-17 and 2016-01-30.
Days = 16 for 2016-01-28, 2016-01-29, 2016-01-30 and 2016-01-31.
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 29, 2017 at 5:03 am
stephen.lightfoot - Tuesday, March 28, 2017 3:48 AMI 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!
<<snip>>
DECLARE @Age INTSET @Age = (0+Convert(Char(8),@AsAtDate,112) - Convert(Char(8),@DOB,112)) / 10000
ALTER FUNCTION [dbo].[il_ElapsedTime]
/*
Calculate the elapsed time between two datetimes
as year, month, day, hour, minute, second, millisecond
such that adding these values using DATEADD to the earlier
value will yield the later value.
This also calculates AGE.
*/
(@Then DATETIME, @Now DATETIME)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT
ds.Years,
mo.Months,
ds.[Days],
r.[Hours],
r.[Minutes],
r.Seconds,
r.Milliseconds
FROM (
SELECT
[Days] = CASE
WHEN DAY(@Now) >= DAY(@Then) THEN DAY(@Now) - DAY(@Then)
ELSE DAY(@Now) + (DATEDIFF(DAY,@Then,EOMONTH(@Then)))
END,
[Years] = (0 + CONVERT(CHAR(8),@Now,112) - CONVERT(CHAR(8),@Then,112)) / 10000
) ds
CROSS APPLY (SELECT [Months] = DATEDIFF(month,DATEADD(YEAR,[Years],DATEADD(day,[Days],@Then)),@Now)) mo
CROSS APPLY (Here's a test harness too:SET NOCOUNT ONIF OBJECT_ID('TempDB..#DOBs') IS NULL BEGIN
DECLARE @Today DATETIME = GETDATE()-13
SELECT DOB = DATEADD(DAY,1-ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),CAST(@Today AS DATETIME))
INTO #DOBs
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)
CREATE UNIQUE CLUSTERED INDEX ucx_Stuff ON #DOBs (DOB)
END
SELECT
d.DOB,
Today = @Today,
x1.*,
NewDate =
DATEADD(YEAR,x1.Years,
DATEADD(MONTH,x1.Months,
DATEADD(DAY,x1.[Days],
DATEADD(HOUR,x1.[Hours],
DATEADD(MINUTE,x1.[Minutes],
DATEADD(SECOND,x1.[Seconds],
DATEADD(MILLISECOND,x1.[Milliseconds],d.DOB)))))))
FROM #DOBs d
CROSS APPLY [dbo].[il_ElapsedTime] (d.DOB, @Today) x1
-- Without the NewDate calculation and with rows blackholed
-- into variables; 50,000 rows in about half a second (SET STATISTICS TIME)
Edit: Sorry, but the code display seems to be completely haywire today. If I enter EDIT mode, the code tags look fine.
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 29, 2017 at 10:08 am
NBSteve - Tuesday, March 28, 2017 4:43 PMm2c2 - 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.
Retested your scenario...and DATEDIFF(HOUR,DOB,GETDATE())/8766 is still correct in SQL2008. ?
March 29, 2017 at 11:45 am
m2c2 - Wednesday, March 29, 2017 10:08 AMNBSteve - Tuesday, March 28, 2017 4:43 PMm2c2 - 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.
Retested your scenario...and DATEDIFF(HOUR,DOB,GETDATE())/8766 is still correct in SQL2008. ?
Leap year age is incorrect on the birthday. This is expected - the expression makes no exception for leap years.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 29, 2017 at 11:55 am
ChrisM@home - Wednesday, March 29, 2017 11:45 AMm2c2 - Wednesday, March 29, 2017 10:08 AMNBSteve - Tuesday, March 28, 2017 4:43 PMm2c2 - 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.
Retested your scenario...and DATEDIFF(HOUR,DOB,GETDATE())/8766 is still correct in SQL2008. ?
Leap year age is incorrect on the birthday. This is expected - the expression makes no exception for leap years.
The newer / 2005+, DATEDIFF function itself makes these types of exceptions.
March 29, 2017 at 12:04 pm
m2c2 - Wednesday, March 29, 2017 10:08 AMNBSteve - Tuesday, March 28, 2017 4:43 PMThen 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.
Retested your scenario...and DATEDIFF(HOUR,DOB,GETDATE())/8766 is still correct in SQL2008. ?
SQL version shouldn't matter, but time of day does with this calculation. The division is assuming exactly 365.25 days in a year (which may be a problem crossing centuries, but that's another discussion). But a year in actual usage is not 365.25 days, it's either 365 or 366 days. You can see the difference with some specific examples:
SELECT DATEDIFF(HOUR,'2012-03-29','2017-03-29 05:59:00')/8766 --returns 4
SELECT DATEDIFF(HOUR,'2012-03-29','2017-03-29 06:00:00')/8766 --returns 5
At less than 5 years and 1/4 days, this returns an age of 4. It doesn't returns 5 years until an extra 6 hours has passed, which is not how we would actually calculate age.
You can also see that problem pop up with this one, which will underreport an age of 0 years :
SELECT DATEDIFF(HOUR,'2016-03-29','2017-03-29 00:00:00')/8766 --returns 0
Or this one, overreporting an age of 2 years beginning 12 hours early:
SELECT DATEDIFF(HOUR,'2015-03-29','2017-03-28 12:00:00')/8766 --returns 2
The positives are that the calculation is quick and easy, it's simple to understand and maintain, it will never be off by more than 12 hours, and it will be correct in well over 99% of situations. If that's good enough to satisfy your requirements, then go for it. But if you need 100% accuracy, this solution (hours/8766) is definitely not your answer.
March 29, 2017 at 12:14 pm
m2c2 - Wednesday, March 29, 2017 11:55 AMChrisM@home - Wednesday, March 29, 2017 11:45 AMm2c2 - Wednesday, March 29, 2017 10:08 AMNBSteve - Tuesday, March 28, 2017 4:43 PMm2c2 - 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.
Retested your scenario...and DATEDIFF(HOUR,DOB,GETDATE())/8766 is still correct in SQL2008. ?
Leap year age is incorrect on the birthday. This is expected - the expression makes no exception for leap years.
The newer / 2005+, DATEDIFF function itself makes these types of exceptions.
It does not work with SQL Server 2012. Try calculating the age of someone who should be one year old today.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 29, 2017 at 12:19 pm
ChrisM@home - Wednesday, March 29, 2017 12:14 PMIt does not work with SQL Server 2012. Try calculating the age of someone who should be one year old today.
See my post immediately above. It's because the technique being used equates 1 year = 365.25 days.
SELECT DATEDIFF(HOUR,'2016-03-29','2017-03-29 00:00:00')/8766 --returns 0
SELECT DATEDIFF(HOUR,'2016-03-29','2017-03-29 06:00:00')/8766 --returns 1
March 29, 2017 at 12:23 pm
NBSteve - Wednesday, March 29, 2017 12:04 PMm2c2 - Wednesday, March 29, 2017 10:08 AMNBSteve - Tuesday, March 28, 2017 4:43 PMThen 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.
Retested your scenario...and DATEDIFF(HOUR,DOB,GETDATE())/8766 is still correct in SQL2008. ?
SQL version shouldn't matter, but time of day does with this calculation. The division is assuming exactly 365.25 days in a year (which may be a problem crossing centuries, but that's another discussion). But a year in actual usage is not 365.25 days, it's either 365 or 366 days. You can see the difference with some specific examples:
SELECT DATEDIFF(HOUR,'2012-03-29','2017-03-29 05:59:00')/8766 --returns 4
SELECT DATEDIFF(HOUR,'2012-03-29','2017-03-29 06:00:00')/8766 --returns 5At less than 5 years and 1/4 days, this returns an age of 4. It doesn't returns 5 years until an extra 6 hours has passed, which is not how we would actually calculate age.
You can also see that problem pop up with this one, which will underreport an age of 0 years :
SELECT DATEDIFF(HOUR,'2016-03-29','2017-03-29 00:00:00')/8766 --returns 0
Or this one, overreporting an age of 2 years beginning 12 hours early:
SELECT DATEDIFF(HOUR,'2015-03-29','2017-03-28 12:00:00')/8766 --returns 2The positives are that the calculation is quick and easy, it's simple to understand and maintain, it will never be off by more than 12 hours, and it will be correct in well over 99% of situations. If that's good enough to satisfy your requirements, then go for it. But if you need 100% accuracy, this solution (hours/8766) is definitely not your answer.
I see, since our situation doesn't require the "birth-moment"...only the "birth-day", the level of accuracy you've been proposing is not necessary.
My apologies if I was wrong, but I had heard in some article quite a while back, that SQL Server's functions all now encompass those more complex date related scenarios.
March 30, 2017 at 3:58 pm
ChrisM@Work - Wednesday, March 29, 2017 1:43 AMAssuming that today is 2017-03-16 (YYYY-MM-DD),
then the Days value is incorrect for DOB's between 2016-01-17 and 2016-01-30.
Days = 16 for 2016-01-28, 2016-01-29, 2016-01-30 and 2016-01-31.
Yes, the number of days is the same for all 4 DOB's, but I don't think it's incorrect.
The "Number of days" value represents not the number of days between DOB and "today", but the number of days since the end of the last full month sincee birthday.
The last full month for all 4 DOB's in your example ends on 28 Feb 2017.
Somebody who's born on any of those 4 days is 1 Year 1 Month old on 28 Feb 2017.
And 16 days later, on 2017-03-16, they all must be, naturally, 1 Year 1 Month 16 days old.
It comes from the non-linearity of the calendar I was referring to at the beginning of the article.
Attempting to count days between dates in non-concecutive months is what makes all the known age calculators to fail.
As soon as we include months into the calculation we should count only days since the last full month before the "today" date.
Number of days in the month of DOB is absolutely irrelevant.
_____________
Code for TallyGenerator
March 31, 2017 at 3:30 am
Sergiy - Thursday, March 30, 2017 3:58 PMChrisM@Work - Wednesday, March 29, 2017 1:43 AMAssuming that today is 2017-03-16 (YYYY-MM-DD),
then the Days value is incorrect for DOB's between 2016-01-17 and 2016-01-30.
Days = 16 for 2016-01-28, 2016-01-29, 2016-01-30 and 2016-01-31.
Yes, the number of days is the same for all 4 DOB's, but I don't think it's incorrect.
The "Number of days" value represents not the number of days between DOB and "today", but the number of days since the end of the last full month sincee birthday.
The last full month for all 4 DOB's in your example ends on 28 Feb 2017.
Somebody who's born on any of those 4 days is 1 Year 1 Month old on 28 Feb 2017.
And 16 days later, on 2017-03-16, they all must be, naturally, 1 Year 1 Month 16 days old.It comes from the non-linearity of the calendar I was referring to at the beginning of the article.
Attempting to count days between dates in non-concecutive months is what makes all the known age calculators to fail.
As soon as we include months into the calculation we should count only days since the last full month before the "today" date.
Number of days in the month of DOB is absolutely irrelevant.
According to this, for a DOB of 2017-09-17 and "asof" date of 2017-11-16,
your method should return only the 16 days of November -but it returns 30 days.
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 31, 2017 at 5:29 am
My method does not return the number of days I November or any other calendar month.
It returns the number of days from the beginning of the current, incomplete month of the age.
In your example, the full month from 2017-10-17 would be completed on 2017-11-17. On 2017-11-16 it's still 0 years and 0 months of age, so we count all 30 days which passed since 2017-10-17.
_____________
Code for TallyGenerator
March 31, 2017 at 6:05 am
Sergiy - Friday, March 31, 2017 5:29 AMMy method does not return the number of days I November or any other calendar month.It returns the number of days from the beginning of the current, incomplete month of the age.In your example, the full month from 2017-10-17 would be completed on 2017-11-17. On 2017-11-16 it's still 0 years and 0 months of age, so we count all 30 days which passed since 2017-10-17.
Our results agree for October, but not September, which is why I chose it for the example.
Try DOB of 2017-09-17 and "asof" date of 2017-11-16.
In fact, try 2017-09-01 to 2017-09-17 with the same As Of date.
Also, for a DOB of 2017-09-30 with the same As Of date, I think the age would be one month and 16 days: October, plus 16 days.
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 31, 2017 at 6:43 am
ChrisM@Work - Friday, March 31, 2017 6:05 AMOur results agree for October, but not September, which is why I chose it for the example.
Try DOB of 2017-09-17 and "asof" date of 2017-11-16.
In fact, try 2017-09-01 to 2017-09-17 with the same As Of date.
Also, for a DOB of 2017-09-30 with the same As Of date, I think the age would be one month and 16 days: October, plus 16 days.
OK,
for DOB 2017-09-17 the subject reaches the age of 1 month on 2017-10-17.
2nd full month would be reached on 2017-11-17.
But the "asof" date is 2017-11-16, so we have not reached 2 moths of age.
Therefore we get the age of 1 month and the number of days from the beginning of 2nd month (2017-10-17) and the "asof" date - 30 days.
For DOB of 2017-09-30 the fist month of age ends on 2017-10-30, and again, the 2nd month of age is never reached.
So, the age is 1 month + number of days between 2017-10-30 and 2017-11-16, which is 17 days.
Number of days in September or October is irrelevant for the age calculations.
OK, to help you understand better.
Here are 3 problems to resolve:
1. DOB = 2017-02-28, ASOF date = 2017-03-28, Age (Months, Days) = ?.
2. DOB = 2017-03-28, ASOF date = 2017-04-01, Age (Months, Days) = ?.
3. DOB = 2017-02-28, ASOF date = 2017-04-01, Age (Months, Days) = ?.
Verification key:
Ages calculated in problems 1 and 2 must add up to the age calculated in the problem 3.
_____________
Code for TallyGenerator
March 31, 2017 at 7:02 am
ChrisM@Work - Friday, March 31, 2017 6:05 AMSergiy - Friday, March 31, 2017 5:29 AMMy method does not return the number of days I November or any other calendar month.It returns the number of days from the beginning of the current, incomplete month of the age.In your example, the full month from 2017-10-17 would be completed on 2017-11-17. On 2017-11-16 it's still 0 years and 0 months of age, so we count all 30 days which passed since 2017-10-17.Our results agree for October, but not September, which is why I chose it for the example.
Try DOB of 2017-09-17 and "asof" date of 2017-11-16.
In fact, try 2017-09-01 to 2017-09-17 with the same As Of date.
Also, for a DOB of 2017-09-30 with the same As Of date, I think the age would be one month and 16 days: October, plus 16 days.
The key to Sergiy's requirement is that you only start counting the days from the point at which you stop counting months, the monthly anniversary so to speak. For any given start day, first you have to find the last monthly anniversary (even if the day doesn't exist), and then start counting days from there.
This is why 1/28/17 - 3/1/17 and 1/31/17 - 3/1/17 are both 1 month, 1 day: First you have to find the monthly anniversary (2/28 and the non-existant 2/31) and then count days from there. For 2017-09-30 to 2017-11-16, first you have to find the nearest monthly anniversary (10/30), and then start counting from there (10/31 plus the 16 days of November).
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply