 |
|
Frank Kulash
|
|
 Frank Kulash is our expert in SQL and PL/SQL. Frank is also an Oracle database administrator for a Massachusetts state agency.
If you have a question for Frank, enter it here.
Also, if you are looking for more on SQL and PL/SQL, view more of Frank's expert answers.
|
|
|
SearchOracle.com SQL and PL/SQL expert Frank Kulash fields many questions about working with dates, days and holidays in Oracle. Check out this collection of scripts and tips to help solve your coding dilemmas.
TABLE OF CONTENTS
Calculating approval date
Calculating AVG date
Dates and DECODE
Determining day of the week
Testing for holidays
Calculating approval date
[ Return to Table of Contents ]
How can I write a function to calculate the approval date which is n days (variable) from a given date, excluding Thursdays and Fridays?
Frank Kulash: So the Approvals Office promises results, sometimes in 0 days, sometimes in 1 day, and so on, but the office is closed on Thursdays and Fridays. The simplest solution I can imagine simulates how you might solve this manually, using a calendar and counting down:
-- approval_dt returns the date that is in_day_cnt "work" days
-- after in_event_dt.
-- A "work" day is any day except Thursday or Friday.
-- If in_day_cnt <= 0, in_event_dt (or the next work day,
-- if in_event_dt is not one) will be returned.
CREATE OR REPLACE FUNCTION approval_dt
(
in_event_dt IN DATE, -- Starting date
in_day_cnt IN NUMBER -- Number of "work" days
)
RETURN DATE
IS
BEGIN
IF TO_CHAR (in_event_dt, 'Dy') IN ('Thu', 'Fri')
THEN -- On off-days, skip ahead with same count
RETURN approval_dt ( in_event_dt + 1,
in_day_cnt
);
ELSIF in_day_cnt <= 0
THEN -- this is the day wanted
RETURN in_event_dt;
ELSE -- skip ahead and count down
RETURN approval_dt ( in_event_dt + 1,
in_day_cnt - 1
);
END IF;
END;
Click for more on calculating and counting dates.
Calculating AVG date
[ Return to Table of Contents ]
Do you know an efficient way to calculate AVG date in Oracle9i?
Frank Kulash: The bad news is: AVG only works on numbers. If you need to average anything else, the best you can do is map the values to numbers, average the numbers and, if necessary and possible, map back to the original domain.
The good news is: That's quite easy with dates in any version of Oracle. Oracle's date arithmetic does it all for you. You don't even have to use TO_NUMBER or TO_DATE.
CLICK for more on AVG date.
Dates and DECODE
[ Return to Table of Contents ]
How do I use the DECODE function with a date field?
Frank Kulash: DECODE only does exact matching. For example, exact values representing "Today" and "Tomorrow" are easy to get, so you might use DECODE like this:
SELECT due_dt,
DECODE ( TRUNC (due_dt),
TRUNC (SYSDATE), 'Today',
TRUNC (SYSDATE + 1), 'Tomorrow',
'Neither'
) AS due_text
FROM tablex;
When you want to compare values (e.g., "Is date a before date b?," or "Is date a between dates b1 and b2?"), then the CASE statement is much more convenient.
SELECT due_dt,
CASE
WHEN TRUNC (due_dt) < TRUNC (SYSDATE)
THEN 'Past'
WHEN TRUNC (due_dt) BETWEEN TRUNC (SYSDATE)
AND TRUNC (SYSDATE) + 6
-- Note: BETWEEN includes both end-points, so
-- SYSDATE+0 through SYSDATE+6 is a full week
THEN 'This Week'
ELSE 'Later'
END AS due_text
FROM tablex;
Click for more on dates and DECODE.
Determining day of the week
[ Return to Table of Contents ]
I need to determine if a particular day of the week exists between two given dates. Is there an Oracle DATE function that does this?
Frank Kulash: There is an Oracle function, NEXT_DAY, that you can use in a fairly elegant solution.
CREATE OR REPLACE FUNCTION dow_between
(
in_dow_text VARCHAR2, -- e.g. 'Mon' or 'Monday'
in_date_1 DATE, -- starting date
in_date_2 DATE -- ending date
)
RETURN DATE
-- *****************************
-- ** d o w _ b e t w e e n **
-- *****************************
-- dow_between returns the a DATE between in_date_1 and
-- in_date_2 whose day-of-the week matches in_dow_text.
-- dow_between returns NULL if there is no such date.
-- in_date_1 must be earlier than in_date_2.
-- Example: dow_between ('SAT', SYSDATE, due_dt)
-- checks if there is a Saturday between today and due_dt,
-- inclusive. If there is, it returns the DATE of the first
-- one. If not, it returns NULL.
-- in_dow_text can be any unique abbreviation (case insensitive)
-- of the day-of-the-week in NLS_LANGUAGE.
IS
return_date DATE := NEXT_DAY ( in_date_1 - 1,
in_dow_text
);
BEGIN
IF TRUNC (return_date) > in_date_2
THEN
return_date := NULL;
END IF;
RETURN return_date;
END dow_between;
/
Click for more on checking day of the week.
Testing for holidays
[ Return to Table of Contents ]
How can I modify your solution for counting the number of work days so it doesn't count dates in my holiday table?
Frank Kulash: If you have a function that takes a date as an argument and returns one value (e.g. a positive number) if that date is a holiday, or another value (e.g. 0) if it's not a holiday, simply add it to the IF statement that tests if you want to skip a date:
CREATE OR REPLACE FUNCTION approval_dt
(
in_event_dt IN DATE, -- Starting date
in_day_cnt IN NUMBER -- Number of "work" days
)
RETURN DATE
IS
BEGIN
IF TO_CHAR (in_event_dt, 'Dy') IN ('Thu', 'Fri')
OR holiday_num (in_event_dt) > 0
THEN -- On off-days, skip ahead with same count
RETURN approval_dt ( in_event_dt + 1,
in_day_cnt
);
ELSIF in_day_cnt <= 0
THEN -- this is the day wanted
RETURN in_event_dt;
ELSE -- skip ahead and count down
RETURN approval_dt ( in_event_dt + 1,
in_day_cnt - 1
);
END IF;
END;
If you don't have a function like that, click here to get one.
For More Information
Featured Topic: Top SQL and PL/SQL advice
Ask the Experts: Frank Kulash
Discussion forum: Oracle Developer
Best Web Links: Languages and development
