• (cs) in reply to eric76
    eric76:
    At least she doesn't appear to have believed that there are 21 months per year.
    Yes, though I would love to see her code that calculates for Floopuary.
  • (cs)

    Lousy Smarch calculations.

  • James (unregistered) in reply to declinator
    declinator:
    ...and both codes are wrong, one of them just a little less wrong than the other. Since February 28, 1899 all five year periods until now have had more than 1825 days. The next five year period with 1825 days will start on March 1, 2096.

    Depending on the data set and how its used this may not be wrong Sometimes you want consistent time scales for metrics. IE months to complete should be calculated as datediff(d, start, end) / 30. Yes some months are not thirty days long but you might want to treat them like that anyway so you can compare data points.

  • Jim (unregistered)

    Cumulative round off error.

    Start with 1 year equals 356 days (a dyslexic input error). Dividing by 2 gives six months = 178 days. Add 178 to get to 12 months = 356 days. Add another 178 to get to 18 months = 534 days. Add another 178 to get to 24 months = 712 days.

    A carpenter would have the same type of error if he cut a short measuring stick and then used that stick to measure a room. Especially if he cut the stick to the wrong length in the first place.

  • (cs)

    After puzzling over this a bit, I finally came to wonder if this code was generated by something. All of the day counts are precisely related to the 356 "days per year".

    So maybe it was generated from a typo'd 365?

  • Dominic (unregistered) in reply to Stewie
    Stewie:
    Indeed, I wonder if the bug would even have been noticed if both of them had been the same.
    It would have been noticed but would also have to be preserved forever
  • Dylan Nicholson (unregistered)

    Odd that nobody's pointed out the obvious redundancy in the range checks, could just be

    WHEN DATEDIFF(day, f.FileDate, @ReportDate) > 1825 THEN '5y+' WHEN DATEDIFF(day, f.FileDate, @ReportDate) > 1460 THEN '4-5y' WHEN DATEDIFF(day, f.FileDate, @ReportDate) > 730 THEN '2-3y' etc. ELSE '0-6m'

    And yes, SQL server evaluates the conditions in the order specified.

  • Scourge of Programmers! (unregistered)

    Looks like you have a bug in both cases.

  • (cs)

    PHB logic: "it doesn't need to be right, it just needs to be consistent"

    maintenance dev logic: "I'll just do what I'm told"

    proper logic: "USE ALL THE UNIT TESTS!!!!"

  • Jim (unregistered)

    I'm gonna assume I'm the first dumb person who doesn't understand why checking for more than 182 seconds for 6 minutes is any more correct than checking for more than 178 seconds.....

  • Jim (unregistered) in reply to Jim
    Jim:
    I'm gonna assume I'm the first dumb person who doesn't understand why checking for more than 182 seconds for 6 minutes is any more correct than checking for more than 178 seconds.....
    Oops, I would be too...month != min

    never mind....

  • foo (unregistered) in reply to Dylan Nicholson
    Dylan Nicholson:
    Odd that nobody's pointed out the obvious redundancy in the range checks, could just be

    WHEN DATEDIFF(day, f.FileDate, @ReportDate) > 1825 THEN '5y+' WHEN DATEDIFF(day, f.FileDate, @ReportDate) > 1460 THEN '4-5y' WHEN DATEDIFF(day, f.FileDate, @ReportDate) > 730 THEN '2-3y' etc. ELSE '0-6m'

    And yes, SQL server evaluates the conditions in the order specified.

    Odd that nobody reads comments anymore.

  • Hugh Joergen (unregistered)

    Oh FFS....like something like the age of a file really needs to be all that accurate....

  • Biggles (unregistered)

    TRWTF is the way the Western World tracks time and date....amirite?

  • gnasher729 (unregistered) in reply to faoileag
    faoileag:
    There is a catch however: the number of days each age group encompasses is not static, due to the fact that different months have different lengths and leap years also have to come into the equation.

    Which is kind of debatable. You are saying that when reports are printed at different times, two children of identical age should be classified differently depending on the vagaries of the calendar.

  • QJo (unregistered) in reply to lucidfox
    lucidfox:
    Anastasia?

    We've found the President's... I mean, Tsar's daughter!

    That's not the Tsar's daughter, that's the woman who used to dance with Hawkwind.

  • Henry Troup (unregistered)

    Faced with the need to have time periods that were rigidly divisible into certain buckets for time-series graphs, we used 180 days for "six months", to divide into nominal 30 day months, but 52*7 or 364 day "years" that graphed as 52 weeks.

    The results of fractions or truncation produced far worse wrong answers than these wrong answers.

  • Dave (unregistered) in reply to Biggles
    Biggles:
    TRWTF is the way the Western World tracks time and date....amirite?
    Exactly. The whole calendar system is a WTF.
  • empty (unregistered) in reply to JimM
    JimM:
    Krunt:
    It may have made more sense to pull a set of results into a temptable and include one column containing the result of the datediff against each row, and then run the outer query with CASE statements comparing it that way however.

    Because what every SQL statement needs is a nested SELECT with a complicated switch statement just to remove the repetition of a DATEDIFF call ;)

    Certainly could use a common table expression to memoize it whether in T-SQL or view. Could also use multiple CTEs to get the "x-y months" calculations. Personally find them quite easy to read. Of course, SQL Server version matters.

    But, yes, the datediff call cost is trivial here.

    And I concur with others that the SQL Server DATEDIFF results are counter intuitive. No longer work on that database but IIRC, I would usually do DATEDIFF on year, month and date when I found it necessary to actually use DATEDIFF.

  • Jibble (unregistered) in reply to Beast
    Beast:
    From what it looks like I'd assume the "6 months difference" they want to have is something like "number of month boundaries crossed minus one, if the current day of month is smaller than the reference date's day of month", so my proposal would be something like: 
    DECLARE @monthdiff int = DATEDIFF(month, f.FileDate, @ReportDate)
IF DATEPART(day, f.FileDate) > DATEPART(day, @ReportDate)
    @monthdiff = @monthdiff - 1

    This gives: 2013-01-31 to 2013-02-01 => 0 2013-01-31 to 2013-02-28 => 0 2013-01-31 to 2013-03-01 => 1 2013-01-31 to 2013-03-31 => 2

    And we get leap years, the different month lengths and all the other problems correct and do not end up with 2013-01-01 being "0-6m" before 2013-07-02.

    Did I miss something?

    Yes...the entire point of this website.

    Clue: We're here to snigger.

  • (cs) in reply to empty
    empty:
    And I concur with others that the SQL Server DATEDIFF results are counter intuitive. No longer work on that database but IIRC, I would usually do DATEDIFF on year, month and date when I found it necessary to actually use DATEDIFF.

    Was thinking about this last night, and there is a better way (given how bad DATEDIFF is at handling edge cases):

    DATEADD.

    It might not work for every date problem, but in this situation it fits perfectly, and it should cope with the edge cases automatically. So instead of

    WHEN DATEDIFF(day, f.FileDate, @ReportDate) <= 182 THEN '0-6m'

    or the agreeably worse

    WHEN DATEDIFF(month, f.FileDate, @ReportDate) <= 6 THEN '0-6m'

    you can use

    WHEN DATEADD(month, 6, f.FileDate) <= @ReportDate THEN '0-6m'

    How many internets do I win? ;)

  • tchize (unregistered)

    Anastasia time is easy

    A normal month is 30 days February is 28 days

    28+5*30 = 178 = 6 month

    1 year is the repeat of that

    and so on.

  • faoileag (unregistered) in reply to Ol' Bob
    Ol' Bob:
    faoileag:
    Valued Service:
    A kid is x months old, where months old = ((current month's day > birth day of month or current month's day is max for month) ? 1 : 0) + (current month + current year * 12) - (birth month + birth year * 12) - 1;
    So a baby born this morning is -1 month old? Are you sure?

    Born this morning? Let's see...

    (current month's day > birth day of month or current month's day is max for month) ? 1 : 0 - evaluates to 0 (current month + current year * 12) = 11 + 2013 * 12 = 24167 (birth month + birth year * 12) = 11 + 2013 * 12 = 24167

    So 0 + 24167 - 24167 = (drum roll.........) ZERO!

    So this formula gives a value of zero months old. Seems right enough.

    Aren't you missing something? Like the final -1 in the original formula?

    So 0 + 24167 - 24167 -1 = (drum roll.........) MINUS ONE!

  • faoileag (unregistered) in reply to anonymous
    anonymous:
    ...as long as she was lysdexic enough...
    Nice, one, that one! I should hunt around in the Urban Dictionary more often...
  • faoileag (unregistered) in reply to chubertdev
    chubertdev:
    PHB logic: "it doesn't need to be right, it just needs to be consistent"

    maintenance dev logic: "I'll just do what I'm told"

    proper logic: "USE ALL THE UNIT TESTS!!!!"

    "Good developer" logic: "Unit tests??? I don't have no time for f*** unit tests!" "Bad developer" logic: "What are those unit tests you keep talking about?"

  • Bernie The Bernie (unregistered)

    Anastasia used a Jewish calender, didn't she? A month defined by the moon is some 29.53 days, just a littel rounding error awy from here calculation. But some Jewish years have 13 months - why did she not take care of that?

  • Tintin (unregistered)

    Well, the code with the '365 days in a year' calculations was probably good enough for what they wanted. Really, sometimes you don't care about total accuracy, just want something reasonably good.

    This was probably for internal stats reports, nothing more significant than that

    Many people interpret '6 months' as being the same as 'half a year', which is pretty much what this gave you.

    If you DO need accuracy, use date addition functions in your DB - eg DATEADD - to add months, rather than using 'date difference' functions. That way you don't get rounding errors.

  • ReallyStupidGuy (unregistered)

    Funny the way both reports are unlogical but it is only an issue because there are diffrences between both reports.

    I think the bug was introduced the moment someone started talking about an age of 0-6 months. How many days can go into one month? A six month period can have a variation of 4 days, depending on the use of these reports this can be a serious deviation.

    Mucht rather I would like to use weeks, days or maybe years (1/2 year), much less open for interpretation. Unless the six months period is really 6 months and an alternative method of naming half a year.

  • Neil (unregistered) in reply to JimM
    JimM:
    empty:
    And I concur with others that the SQL Server DATEDIFF results are counter intuitive. No longer work on that database but IIRC, I would usually do DATEDIFF on year, month and date when I found it necessary to actually use DATEDIFF.
    Was thinking about this last night, and there is a better way (given how bad DATEDIFF is at handling edge cases):
    DATEADD
    It might not work for every date problem, but in this situation it fits perfectly, and it should cope with the edge cases automatically. So instead of
    WHEN DATEDIFF(day, f.FileDate, @ReportDate) <= 182 THEN '0-6m'
    or the agreeably worse
    WHEN DATEDIFF(month, f.FileDate, @ReportDate) <= 6 THEN '0-6m'
    you can use
    WHEN DATEADD(month, 6, f.FileDate) <= @ReportDate THEN '0-6m'
    How many internets do I win? ;)
    Thanks, you saved me the trouble of commenting. Unexpected things might happen at the end of August though.
  • (cs) in reply to Neil
    Neil:
    Thanks, you saved me the trouble of commenting. Unexpected things might happen at the end of August though.

    Care to elaborate? I see no particular problem with August...

  • OneDayWhen (unregistered) in reply to JimM
    JimM:
    ELSE is implicit: each WHEN statement is only evaluated if the previous one was false.

    Correction: if the previous one was not true. In this freaking language, "not true" is not the same as "false" because of three-value logic associated with nulls.

  • (cs) in reply to OneDayWhen
    OneDayWhen:
    In this freaking language, "not true" is not the same as "false" because of three-value logic associated with nulls.

    Correction noted. Although I've got a lot of time for SQL's treatment of logic: at least it's honest enough to admit when it doesn't know something ;)

  • (cs) in reply to JimM
    JimM:
    Neil:
    Thanks, you saved me the trouble of commenting. Unexpected things might happen at the end of August though.

    Care to elaborate? I see no particular problem with August...

    In the southern hemisphere, February has 31 days, while August has 28, except for leap years, when it has 29. let's see how gullible you are :)

  • (cs) in reply to faoileag
    faoileag:
    chubertdev:
    PHB logic: "it doesn't need to be right, it just needs to be consistent"

    maintenance dev logic: "I'll just do what I'm told"

    proper logic: "USE ALL THE UNIT TESTS!!!!"

    "Good developer" logic: "Unit tests??? I don't have no time for f*** unit tests!" "Bad developer" logic: "What are those unit tests you keep talking about?"

    oh, I thought that "good developer" logic would be: "Unit tests??? I'm still waiting on QA for those!! those guys are useless!"

  • Paul Neumann (unregistered) in reply to Bernie The Bernie
    Bernie The Bernie:
    Anastasia used a Jewish calender, didn't she? A month defined by the moon is some 29.53 days, just a littel rounding error awy from here calculation. But some Jewish years have 13 months - why did she not take care of that?
    The 13rd month is technically not part of any year. It is a gift from Allah!
  • (cs) in reply to Paul Neumann
    Paul Neumann:
    Bernie The Bernie:
    Anastasia used a Jewish calender, didn't she? A month defined by the moon is some 29.53 days, just a littel rounding error awy from here calculation. But some Jewish years have 13 months - why did she not take care of that?
    The 13rd month is technically not part of any year. It is a gift from Allah!

    Yahweh to the danger zone!

  • BrianB (unregistered)

    Seems like it could be correct if you don't count holidays.

  • What (unregistered) in reply to declinator

    How do you even know this? Are you a wizard?

  • (cs) in reply to What
    What:
    How do you even know this? Are you a wizard?
    [image]
  • Mario (unregistered)

    I think I know where she got the number from.

    She is probably dyslexic, and put 356 instead of 365 as the days in a year. Then, she multiplied by the correct factors (0.5 for 6 months, 1.5 for 18 months, etc) and got the other numbers.

  • Mario (unregistered) in reply to chubertdev

    WUT? I live in the southern hemisphere, so I can guarantee you that it doesn't work that way. We have February with 28 days on non leap years and 29 on leap years. August is always 31 days. You are probably confused with the fact we have summer when you have winter and vice versa.

  • (cs) in reply to Mario
    Mario:
    WUT? I live in the southern hemisphere, so I can guarantee you that it doesn't work that way. We have February with 28 days on non leap years and 29 on leap years. August is always 31 days. You are probably confused with the fact we have summer when you have winter and vice versa.

    You should have quoted my post.

  • anonymous (unregistered) in reply to chubertdev
    chubertdev:
    Mario:
    WUT? I live in the southern hemisphere, so I can guarantee you that it doesn't work that way. We have February with 28 days on non leap years and 29 on leap years. August is always 31 days. You are probably confused with the fact we have summer when you have winter and vice versa.

    You should have quoted my post.

    Ok, I've quoted your post. Now what?

  • (cs) in reply to anonymous
    anonymous:
    chubertdev:
    Mario:
    WUT? I live in the southern hemisphere, so I can guarantee you that it doesn't work that way. We have February with 28 days on non leap years and 29 on leap years. August is always 31 days. You are probably confused with the fact we have summer when you have winter and vice versa.

    You should have quoted my post.

    Ok, I've quoted your post. Now what?

    Now quote the correct one.

  • anonymous (unregistered) in reply to chubertdev
    chubertdev:
    anonymous:
    chubertdev:
    Mario:
    WUT? I live in the southern hemisphere, so I can guarantee you that it doesn't work that way. We have February with 28 days on non leap years and 29 on leap years. August is always 31 days. You are probably confused with the fact we have summer when you have winter and vice versa.

    You should have quoted my post.

    Ok, I've quoted your post. Now what?

    Now quote the correct one.

    Was your previous post incorrect?

  • (cs) in reply to anonymous
    anonymous:
    chubertdev:
    anonymous:
    chubertdev:
    Mario:
    WUT? I live in the southern hemisphere, so I can guarantee you that it doesn't work that way. We have February with 28 days on non leap years and 29 on leap years. August is always 31 days. You are probably confused with the fact we have summer when you have winter and vice versa.

    You should have quoted my post.

    Ok, I've quoted your post. Now what?

    Now quote the correct one.

    Was your previous post incorrect?

    No, yours was. :)

  • anonymous (unregistered) in reply to chubertdev
    chubertdev:
    anonymous:
    chubertdev:
    anonymous:
    chubertdev:
    Mario:
    WUT? I live in the southern hemisphere, so I can guarantee you that it doesn't work that way. We have February with 28 days on non leap years and 29 on leap years. August is always 31 days. You are probably confused with the fact we have summer when you have winter and vice versa.

    You should have quoted my post.

    Ok, I've quoted your post. Now what?

    Now quote the correct one.

    Was your previous post incorrect?

    No, yours was. :)

    Then the post I quoted was correct? You said it wasn't before.

  • (cs) in reply to anonymous
    anonymous:
    Then the post I quoted was correct? You said it wasn't before.

    Exactly. It was after.

  • Hamilton (unregistered)

    356.. 365.. same thing right?

  • anonymous (unregistered) in reply to chubertdev
    chubertdev:
    anonymous:
    Then the post I quoted was correct? You said it wasn't before.

    Exactly. It was after.

    By quoting it, I made it correct?

Leave a comment on “Interesting Year Length Logic”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article