Oracle: Program not forking correctly in SELECT CASE statement on a date

Question

I'm currently working on a project that behaves differently depending on whether it is a public holiday or not (amongst other constraints, obviously). To this end, I'm trying to create a table that contains the date and what day of the week it is (considering 'holiday' to be an eighth 'day of the week').

I have a table I'm sourcing the list of holidays from, named holiday, which has only one field, holidaydate (DATE datatype). The table I'm trying to put the values into is named daydates, with two fields, day (DATE datatype) and dayofweek (CHAR(10) datatype).

This is the logic as I have it at the moment, while I'm testing.

 INSERT INTO holiday
  (holidaydate)
 SELECT sysdate FROM dual;

 SELECT sysdate,
   (
   CASE
     WHEN sysdate IN
       (SELECT h.holidaydate FROM holiday h)
     THEN 'holiday'  
     ELSE TO_CHAR(sysdate , 'day')
   END)
 FROM dual;

Unfortunately, it currently returns, say, 'Wednesday', rather than 'holiday'. I have a feeling I need to change the WHEN sysdate IN to a WHEN to_char (sysdate, 'dd-mon-yyyy') IN or something along those lines, but I've tried a few variations and they don't seem to have worked thus far.

Any suggestions?

Answer 1

You asked for suggestions so...

• Have you checked what's being inserted into the holiday table by the first statement, to make sure it's correct?
• Have you tried taking sysdate out of the picture (e.g. using a constant) to see if that's what's causing the problem?
• Have you tried replacing the inner select with a constant list?

Answer 2

I think your problem is that it will almost never reach this case:

CASE
     WHEN sysdate IN
       (SELECT h.holidaydate FROM holiday h)

Because, when you insert SYSDATE into the holiday table, it gets inserted with date and time information. So the case you are mentioning is almost never executed.

Try comparing only significant parts of the date on your case.

CASE
   WHEN TRUNC(SYSDATE) IN (SELECT TRUNC(h.holidaydate) from holiday h)

It should work.

Answer 3

In your holiday table, you are only interested in dates and not the time component. Put a constraint in to enforce this and stop anyone making the same mistake you made:

CREATE TABLE HOLIDAY
(holidaydate  DATE NOT NULL 
              CONSTRAINT DateOnlyNoTime 
                 CHECK (holidayDate = TRUNC(holidayDate)));

No doubt you'll want a primary key and some other columns.

The prefered way to remove the date/time portion of a date is to use TRUNC() as above rather than TO_DATE() for performance reasons. Your example query would then become:

SELECT sysdate,
  (
  CASE
    WHEN TRUNC(sysdate) IN
      (SELECT h.holidaydate FROM holiday h)
    THEN 'holiday'
    ELSE TO_CHAR(sysdate , 'day')
  END)
FROM dual;