[IMAGE]Frank Kulash
[IMAGE] 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
[IMAGE] Calculating approval date
[IMAGE] Calculating AVG date
[IMAGE] Dates and DECODE
[IMAGE] Determining day of the week
[IMAGE] 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:
Click for more on calculating and counting dates.
Calculating AVG ...
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
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:
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.
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.
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:
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
