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.

Please create a username to comment.