March 29, 2017 at 8:52 pm
Comments posted to this topic are about the item Performaing a Right and Comprehensive Age Calculation Part 2
_____________
Code for TallyGenerator
March 30, 2017 at 2:38 pm
I would note that to be comprehensive, it needs to have a setting for Leap Day babies as well.
When calculating age for someone whose birthdate is on a leap day (Feb 29th), when the As Of date for the age is in a leap year it's normal, but when the As Of date for the age is not in a leap year, then either Feb 28th or Mar 1 must be used as a "substitute birthdate", and even in one organization both ways may be required to deal with outside customers or agencies or requirements.
March 30, 2017 at 3:23 pm
Nadrek - Thursday, March 30, 2017 2:38 PMI would note that to be comprehensive, it needs to have a setting for Leap Day babies as well.When calculating age for someone whose birthdate is on a leap day (Feb 29th), when the As Of date for the age is in a leap year it's normal, but when the As Of date for the age is not in a leap year, then either Feb 28th or Mar 1 must be used as a "substitute birthdate", and even in one organization both ways may be required to deal with outside customers or agencies or requirements.
I think it works.
28 Feb is a "substitute birthday" for non-leap years:
SELECT * from dbo.AgeCalculation('2016-02-29','2017-02-28',DEFAULT )
Years Months Days
1 0 0
Not sure if anyone agrees with this definition, but at least my neighbour who had her birthday on 29 Feb used the same approach.
_____________
Code for TallyGenerator
March 30, 2017 at 3:32 pm
Sergiy - Thursday, March 30, 2017 3:23 PMNadrek - Thursday, March 30, 2017 2:38 PMI would note that to be comprehensive, it needs to have a setting for Leap Day babies as well.When calculating age for someone whose birthdate is on a leap day (Feb 29th), when the As Of date for the age is in a leap year it's normal, but when the As Of date for the age is not in a leap year, then either Feb 28th or Mar 1 must be used as a "substitute birthdate", and even in one organization both ways may be required to deal with outside customers or agencies or requirements.
I think it works.
28 Feb is a "substitute birthday" for non-leap years:SELECT * from dbo.AgeCalculation('2016-02-29','2017-02-28',DEFAULT )
Years Months Days
1 0 0Not sure if anyone agrees with this definition, but at least my neighbour who had her birthday on 29 Feb used the same approach.
Had this discussion years ago. It sometimes comes down to a jurisdictional call. Some areas will say leaplings turn a year older on February 28th in years that are not leap years and others will say that these individuals turn a year older on March 1st.
There is apparently no universal standard for this.
March 30, 2017 at 4:14 pm
Lynn Pettis - Thursday, March 30, 2017 3:32 PMHad this discussion years ago. It sometimes comes down to a jurisdictional call. Some areas will say leaplings turn a year older on February 28th in years that are not leap years and others will say that these individuals turn a year older on March 1st.
There is apparently no universal standard for this.
Yep, there is no 100% certainty about the right approach.
But I had to pick one to use in my calculations.
So I decided to stay with the one used by DATEADD function in T-SQL.
From my experience, it's the one used in most of situations by people in different countries.
_____________
Code for TallyGenerator
March 30, 2017 at 5:02 pm
Sergiy - Thursday, March 30, 2017 4:14 PMLynn Pettis - Thursday, March 30, 2017 3:32 PMHad this discussion years ago. It sometimes comes down to a jurisdictional call. Some areas will say leaplings turn a year older on February 28th in years that are not leap years and others will say that these individuals turn a year older on March 1st.
There is apparently no universal standard for this.
Yep, there is no 100% certainty about the right approach.
But I had to pick one to use in my calculations.
So I decided to stay with the one used by DATEADD function in T-SQL.
From my experience, it's the one used in most of situations by people in different countries.
That is why I suggested have a parameter to select each of them.
Even in the same country, you may need both calculations, Feb 28 in some queries and Mar 1 in others.
March 31, 2017 at 5:39 am
Nadrek - Thursday, March 30, 2017 5:02 PMSergiy - Thursday, March 30, 2017 4:14 PMLynn Pettis - Thursday, March 30, 2017 3:32 PMHad this discussion years ago. It sometimes comes down to a jurisdictional call. Some areas will say leaplings turn a year older on February 28th in years that are not leap years and others will say that these individuals turn a year older on March 1st.
There is apparently no universal standard for this.
Yep, there is no 100% certainty about the right approach.
But I had to pick one to use in my calculations.
So I decided to stay with the one used by DATEADD function in T-SQL.
From my experience, it's the one used in most of situations by people in different countries.That is why I suggested have a parameter to select each of them.
Even in the same country, you may need both calculations, Feb 28 in some queries and Mar 1 in others.
If we change the behavior for 29 Feb then, for sake of consistency, we need to change it for 31 Jan, 31 Mar, etc.
If 1 year from 29 Feb is 1 Mar, then 1 month from 31 Jan must be 1 Mar.
I don't want to say it would be wrong.
But it would be a big change, with the outcome unexpected by most of the users.
_____________
Code for TallyGenerator
March 31, 2017 at 8:19 am
Sergiy - Friday, March 31, 2017 5:39 AMNadrek - Thursday, March 30, 2017 5:02 PMSergiy - Thursday, March 30, 2017 4:14 PMLynn Pettis - Thursday, March 30, 2017 3:32 PMHad this discussion years ago. It sometimes comes down to a jurisdictional call. Some areas will say leaplings turn a year older on February 28th in years that are not leap years and others will say that these individuals turn a year older on March 1st.
There is apparently no universal standard for this.
Yep, there is no 100% certainty about the right approach.
But I had to pick one to use in my calculations.
So I decided to stay with the one used by DATEADD function in T-SQL.
From my experience, it's the one used in most of situations by people in different countries.That is why I suggested have a parameter to select each of them.
Even in the same country, you may need both calculations, Feb 28 in some queries and Mar 1 in others.
If we change the behavior for 29 Feb then, for sake of consistency, we need to change it for 31 Jan, 31 Mar, etc.
If 1 year from 29 Feb is 1 Mar, then 1 month from 31 Jan must be 1 Mar.I don't want to say it would be wrong.
But it would be a big change, with the outcome unexpected by most of the users.
I don't agree with your logic. The reason is that there will always be January 31st, March 31st, April 30th, May 31st, June 30th, July 31st, August 31st, September 30th, October 31st, November 30th and December 31st in every year. Only February can have a different month end date due to leap years.
March 31, 2017 at 8:46 am
maybe its the way my brain works ...but I cannot accept that 4 different DOBs produce the same result.
SELECT * from dbo.AgeCalculation(' 2017-01-27 ','2017-03-01','md' ) -- 1M 2D
SELECT * from dbo.AgeCalculation(' 2017-01-28 ','2017-03-01','md' ) -- 1M 1D
SELECT * from dbo.AgeCalculation(' 2017-01-29 ','2017-03-01','md' ) -- 1M 1D
SELECT * from dbo.AgeCalculation(' 2017-01-30 ','2017-03-01','md' ) -- 1M 1D
SELECT * from dbo.AgeCalculation(' 2017-01-31 ','2017-03-01','md' ) -- 1M 1D
SELECT * from dbo.AgeCalculation(' 2017-02-01 ','2017-03-01','md' ) -- 1M 0D
SELECT * from dbo.AgeCalculation(' 2017-02-02 ','2017-03-01','md' ) -- 0M 27D
EDIT>...more I think about this, the less reasons I can think of where I would actually need to be so precise. Age months ..possibly, but if number of days is of concern then I will stick with DATEDIFF
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 31, 2017 at 11:42 am
Do you consider this a bug?
SELECT * from dbo.AgeCalculation('2004-02-29','2005-02-27',DEFAULT )
Years Months Days Hours Minutes Seconds
-------------------- -------------------- -------------------- ----------- ----------- -----------
0 11 29 0 0 0
(1 row(s) affected)
March 31, 2017 at 12:51 pm
> maybe its the way my brain works ...but I cannot accept that 4 different DOBs produce the same result.
Let's look at this from another angle.
Please write the date of "exactly 1 month old" against each of those DOB's.
_____________
Code for TallyGenerator
March 31, 2017 at 12:56 pm
Sergiy - Friday, March 31, 2017 12:51 PM> maybe its the way my brain works ...but I cannot accept that 4 different DOBs produce the same result. Let's look at this from another angle. Please write the date of "exactly 1 month old" against each of those DOB's.agrred
agreed...which is why I edited my post to "EDIT>...more I think about this, the less reasons I can think of where I would actually need to be so precise. Age months ..possibly, but if number of days is of concern then I will stick with DATEDIFF
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 31, 2017 at 12:56 pm
>...but I cannot accept that 4 different DOBs produce the same result.
Can you accept that after 30 days from birth one baby will be 1 month 2 days old, and another one would not even reach 1 month of age?
But that's how it is.
_____________
Code for TallyGenerator
March 31, 2017 at 1:06 pm
> Do you consider this a bug?
No, it looks correct.
The year from DOB is not complete, so it's 0 years
11 months of age are reached on 29 Jan, 12th month is not complete (1 day short);
And it's 29 days between 29 Jan and 27 Feb.
_____________
Code for TallyGenerator
March 31, 2017 at 6:30 pm
Lynn Pettis - Friday, March 31, 2017 8:19 AMSergiy - Friday, March 31, 2017 5:39 AMNadrek - Thursday, March 30, 2017 5:02 PMSergiy - Thursday, March 30, 2017 4:14 PMLynn Pettis - Thursday, March 30, 2017 3:32 PMHad this discussion years ago. It sometimes comes down to a jurisdictional call. Some areas will say leaplings turn a year older on February 28th in years that are not leap years and others will say that these individuals turn a year older on March 1st.
There is apparently no universal standard for this.
Yep, there is no 100% certainty about the right approach.
But I had to pick one to use in my calculations.
So I decided to stay with the one used by DATEADD function in T-SQL.
From my experience, it's the one used in most of situations by people in different countries.That is why I suggested have a parameter to select each of them.
Even in the same country, you may need both calculations, Feb 28 in some queries and Mar 1 in others.
If we change the behavior for 29 Feb then, for sake of consistency, we need to change it for 31 Jan, 31 Mar, etc.
If 1 year from 29 Feb is 1 Mar, then 1 month from 31 Jan must be 1 Mar.I don't want to say it would be wrong.
But it would be a big change, with the outcome unexpected by most of the users.I don't agree with your logic. The reason is that there will always be January 31st, March 31st, April 30th, May 31st, June 30th, July 31st, August 31st, September 30th, October 31st, November 30th and December 31st in every year. Only February can have a different month end date due to leap years.
My point it - we should use the same logic for all "lack of days in the month" situations.
If adding a month to May 31 gives us June 30, not July 1, then adding a year to Feb 29 must give Feb 28, not Mar 1.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply