Problem solve Get help with specific problems with your technologies, process and projects.

Calculating approval date, part 1: Counting days

How can I write a function to calculate the approval date which is n days (variable) from a given date, excluding...

Thursdays and Fridays?

So the Approvals Office promises results, sometimes in 0 days, sometimes in 1 day, and so on, but 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;

This is a great solution in several ways:

  • It works
  • You can easily prove that it works
  • It's easy to modify (say the off-days change to Tuesdays and Fridays, or you have to exclude holidays, or the rules change during the summer months)

In fact, the only thing I can see wrong with this solution is that it's slow, especially for large values of in_day_cnt. If this is too slow for you, there is another solution.

Click for part 2 of Frank's response, A faster day counter.

This was last published in September 2003

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.

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

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.