Performaing a Right and Comprehensive Age Calculation Part 2

  • Sergiy

    SSC Guru

    Points: 109836

    Comments posted to this topic are about the item Performaing a Right and Comprehensive Age Calculation Part 2

  • Nadrek

    SSC-Insane

    Points: 20039

    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.

  • Sergiy

    SSC Guru

    Points: 109836

    Nadrek - Thursday, March 30, 2017 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.

    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.

  • Lynn Pettis

    SSC Guru

    Points: 442359

    Sergiy - Thursday, March 30, 2017 3:23 PM

    Nadrek - Thursday, March 30, 2017 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.

    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.

    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.

  • Sergiy

    SSC Guru

    Points: 109836

    Lynn Pettis - Thursday, March 30, 2017 3:32 PM

    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.

    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.

  • Nadrek

    SSC-Insane

    Points: 20039

    Sergiy - Thursday, March 30, 2017 4:14 PM

    Lynn Pettis - Thursday, March 30, 2017 3:32 PM

    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.

    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.

  • Sergiy

    SSC Guru

    Points: 109836

    Nadrek - Thursday, March 30, 2017 5:02 PM

    Sergiy - Thursday, March 30, 2017 4:14 PM

    Lynn Pettis - Thursday, March 30, 2017 3:32 PM

    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.

    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.

  • Lynn Pettis

    SSC Guru

    Points: 442359

    Sergiy - Friday, March 31, 2017 5:39 AM

    Nadrek - Thursday, March 30, 2017 5:02 PM

    Sergiy - Thursday, March 30, 2017 4:14 PM

    Lynn Pettis - Thursday, March 30, 2017 3:32 PM

    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.

    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.

  • J Livingston SQL

    SSC Guru

    Points: 51272

    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

  • graham.goddard90

    SSC Rookie

    Points: 35

    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)

  • Sergiy

    SSC Guru

    Points: 109836

    > 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.

  • J Livingston SQL

    SSC Guru

    Points: 51272

    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

  • Sergiy

    SSC Guru

    Points: 109836

    >...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.

  • Sergiy

    SSC Guru

    Points: 109836

    > 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.

  • Sergiy

    SSC Guru

    Points: 109836

    Lynn Pettis - Friday, March 31, 2017 8:19 AM

    Sergiy - Friday, March 31, 2017 5:39 AM

    Nadrek - Thursday, March 30, 2017 5:02 PM

    Sergiy - Thursday, March 30, 2017 4:14 PM

    Lynn Pettis - Thursday, March 30, 2017 3:32 PM

    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.

    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.

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

You must be logged in to reply to this topic. Login to reply