Avoiding Unexpected Results with DATEDIFF in Year Comparisons

  • Paw Jershauge (Orifarm) - Wednesday, April 11, 2018 12:32 AM

    GeorgeCopeland - Tuesday, April 10, 2018 7:39 AM

    Paw Jershauge (Orifarm) - Tuesday, April 10, 2018 7:24 AM

    I wont EVER come to any of these values, so i don't care... 😉 but ok, if you use these dates before year 1900 and after 2399... then this is not for you :blink:

    I encounter this type of thinking occasionally. As someone who lived and worked through Y2K, I find it disappointing that we have learned so little. There will never be a date hack in any system that I have control of. Any developer who insists on a date hack is on my short list to fire.

    Well, working with financial data, i wont ever come to any dates before 1900 and certainly not date after 2200... so i don't see why i should be bothered, with failures outside my range... But if you think your program will survive 200 years from now.. be my guest at take ALL exceptions that may and may not occur into consideration. :rolleyes:

    So, do you plan on working with the same company and software until you retire or die?  Your situation may change meaning that your hack may break in a new company or with new software.

  • Lynn Pettis - Wednesday, April 11, 2018 9:25 AM

    Paw Jershauge (Orifarm) - Wednesday, April 11, 2018 12:32 AM

    GeorgeCopeland - Tuesday, April 10, 2018 7:39 AM

    Paw Jershauge (Orifarm) - Tuesday, April 10, 2018 7:24 AM

    I wont EVER come to any of these values, so i don't care... 😉 but ok, if you use these dates before year 1900 and after 2399... then this is not for you :blink:

    I encounter this type of thinking occasionally. As someone who lived and worked through Y2K, I find it disappointing that we have learned so little. There will never be a date hack in any system that I have control of. Any developer who insists on a date hack is on my short list to fire.

    Well, working with financial data, i wont ever come to any dates before 1900 and certainly not date after 2200... so i don't see why i should be bothered, with failures outside my range... But if you think your program will survive 200 years from now.. be my guest at take ALL exceptions that may and may not occur into consideration. :rolleyes:

    So, do you plan on working with the same company and software until you retire or die?  Your situation may change meaning that your hack may break in a new company or with new software.

    I corrected it in my original post, but it actually fails for ranges containing the year 2100 ... it'll work for 2400 (as it does for 2000). Try 2098-10-05 to 2102-10-05

  • I am seeing sockpuppets on this thread, have a great day everyone.

  • Eirikur Eiriksson - Wednesday, April 11, 2018 7:29 AM

    Jeff Moden - Wednesday, April 11, 2018 7:23 AM

    Paw Jershauge (Orifarm) - Wednesday, April 11, 2018 6:42 AM

    GeorgeCopeland - Wednesday, April 11, 2018 6:32 AM

    Paw Jershauge (Orifarm) - Wednesday, April 11, 2018 12:32 AM

    Well, working with financial data, i wont ever come to any dates before 1900 and certainly not date after 2200... so i don't see why i should be bothered, with failures outside my range... But if you think your program will survive 200 years from now.. be my guest at take ALL exceptions that may and may not occur into consideration. :rolleyes:

    You are not understanding what I am saying. You don't understand the complexities of the data and you prefer hacks over solutions. This kind of thinking is dangerous and I don't want it anywhere near me.

    I still disagree with you, I fully understand my datatype and ranges, for me, this is not HACK's, its just as valid solution as the one that covers ALL posiple pitfalls. Especially since i know my ranges and datatypes.
    So regardless that you dont want it near you, it still works.

    You might want to reconsider you stance on this subject.  Here's using your code using dates that would be in your useful date range.

    DECLARE @STARTDATE DATETIME = '20180411';
    DECLARE @TODAY     DATETIME = '20190411';
     SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25);

    RESULTS:
    ---------------------------------------
    0

    (1 row(s) affected)

    +365.25
    😎
    FLOOR or CEILING on an approximation of an average can never be exact.

    Your right Jeff... here the code will fail.... :Whistling::Whistling::Whistling::Whistling::Whistling::Whistling:

  • Jeff Moden - Wednesday, April 11, 2018 7:23 AM

    Paw Jershauge (Orifarm) - Wednesday, April 11, 2018 6:42 AM

    GeorgeCopeland - Wednesday, April 11, 2018 6:32 AM

    Paw Jershauge (Orifarm) - Wednesday, April 11, 2018 12:32 AM

    Well, working with financial data, i wont ever come to any dates before 1900 and certainly not date after 2200... so i don't see why i should be bothered, with failures outside my range... But if you think your program will survive 200 years from now.. be my guest at take ALL exceptions that may and may not occur into consideration. :rolleyes:

    You are not understanding what I am saying. You don't understand the complexities of the data and you prefer hacks over solutions. This kind of thinking is dangerous and I don't want it anywhere near me.

    I still disagree with you, I fully understand my datatype and ranges, for me, this is not HACK's, its just as valid solution as the one that covers ALL posiple pitfalls. Especially since i know my ranges and datatypes.
    So regardless that you dont want it near you, it still works.

    You might want to reconsider you stance on this subject.  Here's using your code using dates that would be in your useful date range.

    DECLARE @STARTDATE DATETIME = '20180411';
    DECLARE @TODAY     DATETIME = '20190411';
     SELECT FLOOR(DATEDIFF(DAY, @STARTDATE, @TODAY) * 1.0 / 365.25);

    RESULTS:
    ---------------------------------------
    0

    (1 row(s) affected)

    Your right Jeff, here the code will fail... i stand corrected... :alien::Whistling::alien::Whistling::alien::Whistling::exclamation::exclamation::exclamation::exclamation:

  • Am I over simplifying this...? If you only care to know the difference between the years, why wouldn't you just use DATEPART and return the year from each date and then subtract?

    DECLARE @STARTDATE DATETIME = '2010-10-05';

    DECLARE @TODAY DATETIME = GETDATE();

    SELECT DATEPART(YEAR, @TODAY) - DATEPART(YEAR, @STARTDATE);

  • Dave Wanta - Thursday, April 12, 2018 9:45 PM

    Am I over simplifying this...? If you only care to know the difference between the years, why wouldn't you just use DATEPART and return the year from each date and then subtract?

    DECLARE @STARTDATE DATETIME = '2010-10-05';

    DECLARE @TODAY DATETIME = GETDATE();

    SELECT DATEPART(YEAR, @TODAY) - DATEPART(YEAR, @STARTDATE);

    Yes because if you want to work out someones exact age it wont work.

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

Viewing 7 posts - 46 through 52 (of 52 total)

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