• Anon (unregistered)

    This may be a silly comment and not what the wtf is suppoed to be about...but why on earth is the DateDiff command repeated dozens of times?!?! Surely you would want to do that once, put the result into a variable called, lets say, "dateDiff", and then just that in the rest of the code!

    (Or is this some kind of special reporting tool that doesn't allow variables)

  • Beast (unregistered)

    Additionally: Why not use DATEDIFF(month...) in the frist place?

    Captcha: saluto to all the other poor guys who had to switch from PL/SQL to T-SQL

  • pippo (unregistered) in reply to Anon

    Silly you, DATEDIFF is obviously memoized!

  • declinator (unregistered)

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

  • ANON (unregistered)

    Is it not obvious what happened? She calculated a year with 356 days instead of 365.

  • (cs)

    Not to be an ass, but if she used "month" as a parameter in DATEDIFF instead of "day", there wouldn't be any leap year issues either. I too moved from PL/SQL to T-SQL and suffered, but this is the work of a mad(man|woman).

    Oh, and to spark the discussion: although SQL Server isn't as kick ass, it does have some pretty nice features lacking in Oracle. As with life, it all depends on your situation.

  • Chaarmann (unregistered)

    The real WTF is that both reports are wrong! There are 365 days in a year, but leap years have 366. Also "0-6month" is not the same as "half a year", because February only has 28 days instead of 30 or 31. So the correct caluclation can only be done by getting the month of the start date and the month of the end date and subtracting both. Also Anastasia reported '2-5y' whereas now '2-3y', '3-4y' or '4-5y' will be reported, which is a (maybe unwanted) design change.

    I can imagine how Anastasia got her 'errorneous' day numbers: transposed digits: typing 356 instead of 365. This happens to many programmers, because they learned, 1 byte holds 256 numbers. All other numbers are then calculated by multiplying or dividing this number 356.

  • rob (unregistered)

    Might want to either calculate the difference in months. Either by using 'month' for datepart in date part or using a more complicated expression.

  • Pablo Lerner (unregistered) in reply to declinator

    the problem is that Datediff function can compute month and year differences by it self. There's no need to compute days and calculate months or years by your self

  • Wladimir (unregistered) in reply to Pablo Lerner

    I'm not really familiar with TSQL but a difference in months is a very non-trivial concept. So I checked the documentation on DATEDIFF and sure enough - that's not really what it calculates if you give it "month" as parameter. E.g. it will give you 1 as difference in months between 2013-01-31 and 2013-02-01 - one month boundary crossed. In other words, 2013-01-31 to 2013-07-01 (151 days) and 2013-01-01 to 2013-07-31 (211 days) are both "six months intervals" according to that function. That's significantly worse than "Anastasia Time".

  • Blue! (unregistered) in reply to Wladimir
    Wladimir:
    I'm not really familiar with TSQL but a difference in months is a very non-trivial concept. So I checked the documentation on DATEDIFF and sure enough - that's not really what it calculates if you give it "month" as parameter. E.g. it will give you 1 as difference in months between 2013-01-31 and 2013-02-01 - one month boundary crossed. In other words, 2013-01-31 to 2013-07-01 (151 days) and 2013-01-01 to 2013-07-31 (211 days) are both "six months intervals" according to that function. That's significantly worse than "Anastasia Time".
    That needs to be blue.
  • Krunt (unregistered) in reply to Anon
    Anon:
    This may be a silly comment and not what the wtf is suppoed to be about...but why on earth is the DateDiff command repeated dozens of times?!?! Surely you would want to do that once, put the result into a variable called, lets say, "dateDiff", and then just that in the rest of the code!

    'f.filedate' in this instance is referring to a column, so it cannot be stored into a variable given that it will be different for every row.

    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.

  • (cs) in reply to Krunt
    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 ;)

    I don't buy that this was a typo: I can see you typing it wrong, but not calculating all your other dates from the typo wrong - surely at some point you're going to look back and say "so, five years is five times... hang on: 356? Nooooo" then doing over. No, it looks to me like Anastasia genuinely thought there were 356 days in a year.

  • Doing it properly. (unregistered)

    Pfft... everyone knows that years have 256 days, each of which is 123268.5 seconds long.

  • Beast (unregistered) in reply to Wladimir
    Wladimir:
    I'm not really familiar with TSQL but a difference in months is a very non-trivial concept.

    That's right. 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?

  • faoileag (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"
    Actually, looking at the "groups" created, the first thing that came to mind were age groups for children.

    However, then you need their age, and that is something that you don't calculate by the means of crossed months boundaries. A baby born on the 30th of June is not one month old come the 1st of July.

    So in theory, calculating the days between the date in the database (probably the dob) and the date on which the report is generated is the way to do it.

    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.

    It is probably excusable not to calculate the age groups to the needed precision as this looks like it was meant for some statistical report; but it is completely unexcusable for Anastasia to work on the assumption that a year has 356 days instead of the more generally accepted number of 365 days (excluding leap years).

    And that the 356 days are not a one-off typo with accidentally mixed up digits can be seen from the 178 days Anastasia uses for a six month period.

  • eric (unregistered) in reply to declinator

    Might want to read the logic in the first example again. Hint.... >

  • (cs) in reply to Beast
    Beast:
    Did I miss something?

    You're assuming we're using T-SQL in a stored procedure. But there's no way to tell from the OP. This could be a column calculation in a view (in fact as this is part of a report I'd be inclined to say it's likely to be part of a view). So you need to refactor your example to work without using holding variables ;)

  • Beast (unregistered) in reply to JimM
    JimM:
    You're assuming we're using T-SQL in a stored procedure. But there's no way to tell from the OP. This could be a column calculation in a view (in fact as this is part of a report I'd be inclined to say it's *likely* to be part of a view). So you need to refactor your example to work without using holding variables ;)

    Not that big a problem. I'd actually try a user created function for it, passing in the two dates. If this does not work performance-wise - I've been told that user defined functions are evil in T-SQL, although I have not yet made any tests myself - you'd have to include everything in the CASE part (similar to the OP). This of course would be quite cumbersome to read, but at least we don't get a result where 2012-01-01 to 2016-12-31 is "5y+" and the like.

  • anonymous (unregistered) in reply to faoileag
    faoileag:
    And that the 356 days are not a one-off typo with accidentally mixed up digits can be seen from the 178 days Anastasia uses for a six month period.
    Not really. She probably calculated the length of a month and then reused that over and over: 356/12 [M+]*6 (note 6m length) [MR]*12 (note 12m length) [MR]*18 (note 18m length)... or, 356/12*6 (note 6m length) /6*12 (note 12m length) /12*18 (note 18m length) etc.
  • (cs)

    Reminds me of a daycare nearby that claims they'll care for kids "0.3 - 1 years old".

    I THINK they meant "3 month old", and not 3.6 months old. "This month has 31 days. Is your child exactly 3 months and 18 days and 14 hours and 24 minutes old?"

    Really, anyone trying to measure months by count of days is TRWTF.

  • Chelloveck (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.

    It's a simple case of "good enough". The code is condensing number of days into number of months with (at best) a 6 month granularity. Miscategorizing a date due to a leap day off-by-one error is unlikely to cause any significant incorrectness. If you really need to know to the day then you don't dick around with 6-month wide buckets in the first place.

    Don't they teach kids the concept of significant digits anymore? It's not just for trick questions on maths tests. It really should be applied in real-life story problems.

  • OneDayWhen (unregistered)

    TRWTFs: 1) the copy and paste "solution", 2) calculating values on the fly rather than a one-time calculation for every date for the next 20 years, putting the result in a table named 'Calendar'.

  • Jo (unregistered)

    Oh, those age groups are okay. In fact if you say "age of three months", in most cases, you really mean "age of 365.25/4 days". If you do the calculations by calendar rules, your statistics will always be noticeably off for February (and we're talking reports, i.e. statistics, here).

    Using different periods in different queries - well, the usual WTFery in production I guess.

    Captcha: facilisi - I made it simpler (when talking Lipsum-garbled Latin)

  • Stewie (unregistered) in reply to Chelloveck

    Indeed, I wonder if the bug would even have been noticed if both of them had been the same.

  • (cs)

    Anastasia?

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

  • faoileag (unregistered) in reply to anonymous
    anonymous:
    faoileag:
    And that the 356 days are not a one-off typo with accidentally mixed up digits can be seen from the 178 days Anastasia uses for a six month period.
    Not really. She probably calculated the length of a month and then reused that over and over: 356/12 [M+]*6 (note 6m length) [MR]*12 (note 12m length) [MR]*18 (note 18m length)
    That's what I said - A typo would mean thinking 365 and typing 356, whereas using 356 as a base in calculations clearly shows that Anastasia has had 356 in her mind when she wrote the routine.

    Ah, well. Who knows what planet she comes from.

  • (cs)

    What's the problem? Anastasia used a lunar year, rather than your boring solar year.

    Maybe she just really likes Islamic Leap Years.

  • Valued Service (unregistered)

    If the statistics are for measurement with exactness needed, then do age by days and be transparent of that. If the statistics are for pricing, then use the conventional expectations. 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;

    That's how the average person determines age in months.

  • faoileag (unregistered) in reply to Valued Service
    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?
  • rebuke (unregistered)

    TRWTF is someone who finds the DATEDIFF function but then doesn't think to try putting 'month' instead of 'day' and solving all their problems in one go...

  • (cs) in reply to rebuke
    rebuke:
    TRWTF is someone who finds the DATEDIFF function but then doesn't think to try putting 'month' instead of 'day' and solving all their problems in one go...

    TRWTF is someone who thinks that the DATEDIFF function is the solution to all their problems :p

    (hint: read the rest of the comments to find out why it isn't)

  • Valued Service (unregistered) in reply to faoileag
    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?

    (current month's day > birth day of month) yes if you count time of day.

    So, no, it won't be -1.

  • foo (unregistered) in reply to faoileag
    faoileag:
    anonymous:
    faoileag:
    And that the 356 days are not a one-off typo with accidentally mixed up digits can be seen from the 178 days Anastasia uses for a six month period.
    Not really. She probably calculated the length of a month and then reused that over and over: 356/12 [M+]*6 (note 6m length) [MR]*12 (note 12m length) [MR]*18 (note 18m length)
    That's what I said - A typo would mean thinking 365 and typing 356, whereas using 356 as a base in calculations clearly shows that Anastasia has had 356 in her mind when she wrote the routine
    A typo in her brain. :)

    But seriously, you're giving too much credit on how people do calculations. They type (and sometimes typo) numbers into their calculator or spreadsheet and happily copy the results from them without any sanity checking. The result may be off by orders of magnitude, they don't notice or care because "the machine said so".

    That's why some people consider using calculators too much (esp. in school) dangerous.

  • foo (unregistered)

    Doesn't this freaking language have "else", or are Glidder and Anastasia (AKA Gliddero and Anastasiao) just shy of such newfangled stuff and prefer to write everything twice?

  • just some DBA (unregistered)

    TRWTF is hard coding your ranges rather than, I don't know, storing them in the database one time and just joining that table whenever you needed.

  • (cs) in reply to Valued Service
    Valued Service:
    faoileag:
    So a baby born this morning is -1 month old? Are you sure?

    (current month's day > birth day of month) yes if you count time of day.

    You must be using some strange definition of the word day that I wasn't previous aware of.

    or do you contend that (in my case) 4 > 4?

  • (cs) in reply to foo
    foo:
    Doesn't this freaking language have "else"

    This is a T-SQL CASE statement, so no, it doesn't (or at least, not the way you mean: ELSE in this context actually means DEFAULT). But it does shortcut, so in fact every line is making on unnecessary comparison. Or, to put it another way, the ELSE is implicit: each WHEN statement is only evaluated if the previous one was false.

  • Jim Howard (unregistered)

    I know nothing about database coding, but in the client world we have well thought out libraries for date/time calculations.

    Because date/time calculations are very hard, and not something that should be attempted by someone not willing to devote a lot of time to design and testing of her code.

    Is every TSQL programmer expected to reinvent the date/time wheel? If so, that would explain a lot of (bad) things.

  • (cs) in reply to Chaarmann
    Chaarmann:
    The real WTF is that both reports are wrong! There are 365 days in a year, but leap years have 366. Also "0-6month" is not the same as "half a year", because February only has 28 days instead of 30 or 31. So the correct caluclation can only be done by getting the month of the start date and the month of the end date and subtracting both. Also Anastasia reported '2-5y' whereas now '2-3y', '3-4y' or '4-5y' will be reported, which is a (maybe unwanted) design change.

    I can imagine how Anastasia got her 'errorneous' day numbers: transposed digits: typing 356 instead of 365. This happens to many programmers, because they learned, 1 byte holds 256 numbers. All other numbers are then calculated by multiplying or dividing this number 356.

    "half a year in months" != "half a year in days"

    granularity...

  • (cs)

    And I thought that health care signup web sites were really wacky. Maybe Anastasia was involved there.

    Then again, given that the health care project was a "known problem" bunches of years ago, I kinda doubt it. Those monkeys are WTFs all themselves.

    When will we get stories from that project??

  • (cs) in reply to JimM
    JimM:
    Valued Service:

    (current month's day > birth day of month) if you count time of day.

    Do you contend that (in my case) 4 > 4?

    Happy birthday JimM!

  • Anomaly (unregistered)

    It depends how you determine you want to count "months", i'll use decades as an analogy. I'm 24 and I've lived in 4 different decades. The decades are predetermined lengths of time (10 years). I was born in 89 (80's), lived through 90's and 0's, And now i'm living through the 10's.

    I've lived in four decades.

    Now back to the topic, if the creators are intending for the calculations to be end of month based, or actual day based thats two different calculations. equivalent to how many months have I accumulated, versus how many months i've lived.

    Born on Jan 31st, on Feb 1st depending on how you want to quantify its either one month obtained (end of month) or 0 months obtained (by day)

  • (cs)

    Apparently she was working on a calendar with 29 day months.

    Maybe she is banking the extra days for a big vacation.

  • Ol' Bob (unregistered)

    The real WTF is that they had a DBA writing code. From "How To Be A Real DBA For Dummy's And Coder's Who Only Wish They Were"...

    1. Real DBA's don't program in COBOL.
    2. Real DBA's don't program in SQL.
    3. Real DBA's don't program. Programming is for clerks and masochists.
    4. Real DBA's administrate. That's why they're called Data Base Administrators. Not "programmers". Not "managers". Not "clerks" or "masochists". ADMINISTRATORS.
    5. Real DBA's may occasionally deign to glance contemptuously at some DDL written by some programmer (= retarded dweeb). It will always be "wrong", of course, for in the eyes of a Real DBA NO DDL written by anyone else is ever correct - and since a Real DBA would never, EVER write a single line of DDL then of course NO DDL is ever correct. QED.
    6. Real DBA's will, in practical terms, never be found at their desks. A Real DBA is always on his way TO or on his way back FROM a meeting with important people with whom mere developers are not fit to meet. As it so happens, the cafeteria is always in the path to and/or from said meeting, which is why the DBA can very nearly always be found in the cafeteria.
    7. Real DBA's are too busy to ever go to lunch. This explains why they are nearly always found in the cafeteria, grazing on machine food, which they do not enjoy but consume merely to satisfy the immense caloric requirements generated by their intense mental activity.
    8. Real DBA's never think. Thinking is for directors and VP's and other management dweebs. Real DBA's spend their days cogitating upon the infinite. This gives them the appearance of being asleep at their pseudo-private table in the cafeteria, but do not be deceived! Those foolish enough to disturb them will find themselves subject to immediate and intense attack using the secret martial art of the DBA's - Dorito Foo! Woe betide the foolish programmer who disturbs the reverie of the DBA..!!
    9. Real DBA's are never in the office, unless called in for an important meeting. Real DBA's work from home, usually between the hours of midnight and 4:30 AM. These hours are precious to the DBA because due to contortions in the space-time continuum it is possible for a single DBA to accomplish more in those four-and-a-half hours than a mere programmer or analyst can get done in two weeks.
    10. Real DBA's are heard of, but never heard from. Real DBA's do not respond to emails. Real DBA's do not listen to voicemail. Real DBA's have a direct network connection to God, with whom the DBA is willing to IM if God is sufficiently polite.
    11. Real DBA's do not admit their own existence.
    12. I am not a Real DBA.
  • anonymous (unregistered) in reply to faoileag
    faoileag:
    anonymous:
    faoileag:
    And that the 356 days are not a one-off typo with accidentally mixed up digits can be seen from the 178 days Anastasia uses for a six month period.
    Not really. She probably calculated the length of a month and then reused that over and over: 356/12 [M+]*6 (note 6m length) [MR]*12 (note 12m length) [MR]*18 (note 18m length)
    That's what I said - A typo would mean thinking 365 and typing 356, whereas using 356 as a base in calculations clearly shows that Anastasia has had 356 in her mind when she wrote the routine.

    Ah, well. Who knows what planet she comes from.

    And what I said was that she'd only need to typo the 356 once, and the error could perpetuate to all of her numbers as long as she was lysdexic enough not to notice that the number for the 12 month category was 356 days instead of 365 days.

    You're probably right, though. She probably thought that a year was 356 days long.

  • Ol' Bob (unregistered) in reply to Anon
    Anon:
    This may be a silly comment and not what the wtf is suppoed to be about...but why on earth is the DateDiff command repeated dozens of times?!?! Surely you would want to do that once, put the result into a variable called, lets say, "dateDiff", and then just that in the rest of the code!

    Optimization is for anal-retentive geeks and assembler weenies! If the compiler is stupid enough to generate a pile of individual function calls - well, I guess we need to write a new compiler!

  • Ol' Bob (unregistered) in reply to faoileag
    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.

    But tomorrow the kid will be reported as being one month old, because tomorrow the "current month's day" will be greater than birth day of month (today). Oh, the shame! Oh, the humanity...!!!!!

    YMMV.

  • foo (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.

    But tomorrow the kid will be reported as being one month old, because tomorrow the "current month's day" will be greater than birth day of month (today). Oh, the shame! Oh, the humanity...!!!!!

    YMMV.

    Real DBAs do not subtract one.

  • eric76 (unregistered) in reply to anonymous
    anonymous:
    faoileag:
    anonymous:
    faoileag:
    And that the 356 days are not a one-off typo with accidentally mixed up digits can be seen from the 178 days Anastasia uses for a six month period.
    Not really. She probably calculated the length of a month and then reused that over and over: 356/12 [M+]*6 (note 6m length) [MR]*12 (note 12m length) [MR]*18 (note 18m length)
    That's what I said - A typo would mean thinking 365 and typing 356, whereas using 356 as a base in calculations clearly shows that Anastasia has had 356 in her mind when she wrote the routine.

    Ah, well. Who knows what planet she comes from.

    And what I said was that she'd only need to typo the 356 once, and the error could perpetuate to all of her numbers as long as she was lysdexic enough not to notice that the number for the 12 month category was 356 days instead of 365 days.

    You're probably right, though. She probably thought that a year was 356 days long.

    At least she doesn't appear to have believed that there are 21 months per year.

Leave a comment on “Interesting Year Length Logic”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article