08 March 2011

Confusion caused by reference to year in question (2082)

The 7 March quiz asked:

"Which of the choices define a function that accepts any date in 2011 and returns the date of the first day of the next quarter from that date? Note: quarters begin on the following dates: 1 January, 1 April, 1 July, and 1 October."

Two players raised a concern about my reference to "any date in 2011," as follows:

"I had a problem with todays quiz. I answered it the way I feel you intended it to be, on the merits of functionality. But by adding a specific year into the question, some might feel that you need to set up a check to see if it is that year that is coming in through the parameter. therefore, no answers would be correct."

"I found the "accepts any date in 2011" misleading - to me this suggests an exclusion of non-2011 dates which is not in any of the choices. The only implicit restriction that I could see was that the passed date needed to be less than 01-Oct-9999."

My response: I see what you mean and if the question constituted technical specifications for a program to be written for an application, then I would also agree. There is no validation in any of the solutions for this restriction on dates. This is, however, a quiz - and your job was to determine if an implementation would in fact accept any date in 2011 and...so on. It doesn't say anything about rejecting a date not in the year. So I don't see any reason to change the scoring on this turn.

You might be interested to know why I would add this "clause" to the question - it has lots to do with my effort to create interesting quizzes, but also with some odd aspects of Oracle's handling of dates.

First, I like to offer more than one correct solution for most of my quizzes. That keeps players "on their toes" and also often serves as a way to demonstrate that there is always multiple solutions to a given problem in programming, and all solutions are not equal.

Many times, we end up writing long, elaborate algorithms, all the time thinking to ourselves: "It shouldn't be this hard, it can't take all this code...surely there is a better way?" But we don't listen to that voice. I encourage you to listen, and be ready to back away from code you have written, and challenge your assumptions.

So I decided to include this choice as an option:
CREATE OR REPLACE FUNCTION plch_day1_nextq (date_in IN DATE)
   c_year    CONSTANT INTEGER := TO_CHAR (date_in, 'yyyy');
   c_date   CONSTANT DATE := TRUNC (date_in);

   FUNCTION date_in_year (part_in IN VARCHAR2)
      RETURN TO_DATE (c_year || '-' || part_in, 'YYYY-MM-DD');
   END date_in_year;
             WHEN c_date < date_in_year ('04-01')
                date_in_year ('04-01')
             WHEN c_date < date_in_year ('07-01')
                date_in_year ('07-01')
             WHEN c_date < date_in_year ('10-01')
                date_in_year ('10-01')
                TO_DATE (TO_CHAR (c_year + 1) || '-01-01', 'YYYY-MM-DD')
It is a great example of "over-engineering" a solution, doing it with brute force, rather than simply finding the best built-in(s) to do the job.

But I wanted it to be a valid solution and that immediately leads to questions like "Will it handle any date? What about valid date ranges for Oracle? Do those vary by version?"

It turns out, in fact, that if I want that monstrous function to be a valid choice, there is more to address than simply saying "Any date between" Oracle's supported date range. One of my reviewers noted that this function also will fail if a date with a year number of 0 is passed into it, raising this error:

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Now, I suppose I could have continued my work on this function until it handled all scenarios, but quite honestly I am a bit busy these days and...who knows what other odd behavior a player would uncover...

So I "slapped on" the reference to dates in 2011. It is, in other words, an artificial constraint whose purpose is to simplify the choices and my life.


  1. Hello All,
    Do you know what is most annoying for a player
    that KNOWS which is the most correct choice ?

    It is the fact that he should "spend time" compiling by eye the verbose choice (I bet most/all of those who answered 100% correctly
    spent their time there !) ...

    I myself do have exactly the function, with its best version (the short one) already implemented in one of my applications
    for a few good years by now ...

    When it comes to compiling someone else's code
    "by eye", then everybody has his own style
    of making things verbose on purpose ...
    so the feeling is anyway a kind of let's call it "a small punishment" ...

    Thanks & Best Regards,
    and, Steven, keep going on for all of us !!!

  2. What I did by the complex answer? It is so big, someone spent a lot of time in writing it and there are no direct visual clues of errors. It must be correct. So I spent say 15 seconds on that one. It costed me more time to be sure that this function NEXT_QUARTER (or something like that)doesn't exists.

    BTW, it is not so strange that zero is not a correct answer. The year 0 does not exists. Has something to do with the fact that there is no Roman numeral for zero. That is why the 21st century started on 1-1-2001 and not on 1-1-2000. Suggestion for a quiz? Nah, to much discussion about when a century starts and ends to be really a nice quiz.

  3. I was grateful for that 2011 restriction. It meant I didn't have to worry about leap years, or that weird years of 1582 and 1752 when SOME countries switched between the Julian and Gregorian calendars and missed 11 days. Other countries adopted it later, requiring a different number of days adjustment.

    I also didn't have to worry about BC years. If you really want to irritate someone, store a date of 2011 BC. It will take them ages to work out what the issue is. Oh, and the intricate function doesn't work for BC years as the 'BC' component gets dropped when deriving c_year.

  4. There is huge complexity in manual date calculations (as has been pointed out) and the restriction to 2011 certainly simplified the process of checking the answers.

    I feel that the question suggests a functional requirement rather than a restriction of data cases (an alternative would be "... a function which, when passed any date in 2011, returns ...").

  5. Excellent suggestion, Furry One. I will change the question text as you suggest.

    Cheers, SF

  6. I didn't misinterpret the question (I just stuffed up the +90 days - silly considering the question I submitted a few days ago), but I can appreciate others making comments about 2011.

    I also thought a slight reword would remove all ambiguity, but couldn't think of the right words - good work Furry One - I agree ;-)

  7. @Wim de Lange
    You cannot simply say that year 0 does not exists,
    the story is much more involved, see