Q

Testing for holidays, part 2

This Content Component encountered an error

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.

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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close