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