# 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?

#### Requires Free Membership to View

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.

#### More News and Tutorials

This was first published in September 2003