I saw your previous solution for counting the number of work days. How can I modify it to not count dates in my...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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' );
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.