Testing for holidays, part 2

I saw your previous solution for counting the number of work days. How can I modify it to not count dates in my holiday table? 
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.

    Requires Free Membership to View

    Login

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'
);

This was first published in September 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top