Post reply

Date and Time Calculations Made Easy with EOMONTH, DATEDIFF, and DATEADD

  • May 9, 2025 at 12:00 am

    #4585277

    Comments posted to this topic are about the item Date and Time Calculations Made Easy with EOMONTH, DATEDIFF, and DATEADD

  • May 9, 2025 at 10:27 am

    #4589360

    your code for exact age fails for people born on leap days and you are getting the age in non leap years

    to get the correct answer, use my FLOOR calculation

    DECLARE @BirthdateLeap DATE = '1980-02-29';
    DECLARE @TestDate1 DATE = '2025-02-27';
    DECLARE @TestDate2 DATE = '2025-02-28';
    DECLARE @TestDate3 DATE = '2025-03-01';

    select 
    CASE 
    WHEN DATEADD(year, DATEDIFF(year, @BirthdateLeap, @TestDate1), @BirthdateLeap) > @TestDate1
    THEN DATEDIFF(year, @BirthdateLeap, @TestDate1) - 1
    ELSE DATEDIFF(year, @BirthdateLeap, @TestDate1)
    END AS ExactAgeLeapFeb27
    ,CASE 
    WHEN DATEADD(year, DATEDIFF(year, @BirthdateLeap, @TestDate2), @BirthdateLeap) > @TestDate2
    THEN DATEDIFF(year, @BirthdateLeap, @TestDate2) - 1
    ELSE DATEDIFF(year, @BirthdateLeap, @TestDate2)
    END AS ExactAgeLeapFeb28
    ,CASE 
    WHEN DATEADD(year, DATEDIFF(year, @BirthdateLeap, @TestDate3), @BirthdateLeap) > @TestDate3
    THEN DATEDIFF(year, @BirthdateLeap, @TestDate3) - 1
    ELSE DATEDIFF(year, @BirthdateLeap, @TestDate3)
    END AS ExactAgeLeapMar01
    --correct age calculation
    ,FLOOR((CONVERT(INT,CONVERT(CHAR(8),@TestDate1,112))- CONVERT(INT,CONVERT(CHAR(8),@BirthdateLeap,112)))/10000)as CorrectLeapAgeFeb27
    ,FLOOR((CONVERT(INT,CONVERT(CHAR(8),@TestDate2,112))- CONVERT(INT,CONVERT(CHAR(8),@BirthdateLeap,112)))/10000)as CorrectLeapAgeFeb28
    ,FLOOR((CONVERT(INT,CONVERT(CHAR(8),@TestDate3,112))- CONVERT(INT,CONVERT(CHAR(8),@BirthdateLeap,112)))/10000)as CorrectLeapAgeMar01

    • ExactAgeLeapFeb27 - 44
    • ExactAgeLeapFeb28 - 45
    • ExactAgeLeapMar01 - 45
    • CorrectLeapAgeFeb27 - 44
    • CorrectLeapAgeFeb28 - 44
    • CorrectLeapAgeMar01 - 45

    in the UK, I don't know about other countries, you don't legally get your birthday until March 1st on non-leap years,

    https://www.legislation.gov.uk/ukpga/1991/50/section/6/1991-09-25?view=plain

    Attainment of age.

    (1)The time at which a person attains a particular age expressed in years shall be taken to be the beginning of the relevant anniversary of the date of his birth.

    (2)Where a person has been born on 29th February in a leap year, the relevant anniversary in any year other than a leap year shall be taken to be 1st March.

    best wishes

    Andrew

     

  • May 9, 2025 at 10:29 am

    #4589361

    sorry duplicate post due to time out

    • This reply was modified 5 days, 19 hours ago by  andrew_dale.

Viewing 3 posts - 1 through 2 (of 2 total)

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