Testing for holidays, part 2

CREATE TABLE  holiday
(
    id         NUMBER (10)    PRIMARY KEY,
    start_dt   DATE           NOT NULL,
    end_dt     DATE           NOT NULL,
    cmnt       VARCHAR2 (100)
);

I've attached some sample data for the table.

The following is continued from part 1.

Code for holiday_num Function

When comparing dates in Oracle, always remember that they contain a time component. When you ask "Is SYSDATE + 21 a holiday?", you may think you're asking "Is October 13 a holiday?", but actually you're asking "Is October 13, 1:08 p.m., a holiday?". Is the difference significant? It is if you inserted '13-Oct-2003' as the end of the holiday, because Oracle interprets that as being 12 midnight at the beginning of October 13, which is before 1:08 in the afternoon of the same day. Use the TRUNC function if you want to ignore the time of day.

--  holiday_num returns a positive number if in_dt is
--  a holiday according to the holiday table (that is,
--  if in_dt falls between start_dt and end_dt (inclusive)
--  for some row in holiday.
--    The number returned is the id for that row (or for
--  an arbitrary one of the rows, if there are several.)
--    If in_dt is not a holiday, 0 is returned.  (Therefore,
--  don't use 0 as an id.)

CREATE OR REPLACE FUNCTION holiday_num
(
    in_dt  DATE
)
RETURN  NUMBER
IS
    first_id  holiday.id%TYPE;
BEGIN
    SELECT  id
     INTO   first_id
     FROM   holiday
     WHERE  TRUNC (in_dt) BETWEEN TRUNC (start_dt)
                              AND (end_dt)
       AND  ROWNUM = 1;

    RETURN  first_id;    -- It is a holiday
EXCEPTION
    WHEN  NO_DATA_FOUND
        THEN  RETURN 0;  -- Not a holiday
    WHEN  OTHERS
        THEN  RETURN NULL;
END  holiday_num;

Sample Data

INSERT INTO  holiday
(
    id,
    start_dt,
    end_dt,
    cmnt
) 
VALUES
(
    holiday_id_seq.NEXTVAL,
    '13-Oct-2003',
    '13-Oct-2003',
    'Columbus Day'
);

INSERT INTO  holiday
(
    id,
    start_dt,
    end_dt,
    cmnt
) 
VALUES
(
    holiday_id_seq.NEXTVAL,
    '27-Nov-2003',
    '28-Nov-2003',
    'Thanksgiving (Thursday); business closed Friday, too'
);