Checking the day of the week

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;
/

The NEXT_DAY (d, c) function has a couple of quirks that dow_between handles or inherits:

• It returns a DATE after d, never the same as d. That's why return_date is initialized by NEXT_DAY (in_date_1 - 1, ...)
• It returns a DATE with the same time of day as the argument d. That's why the TRUNC command in the IF statement is necessary.
• In some languages, the argument c may be abbreviated. When NLS_LANGUAGE = English, for example, the first two letters of the weekday names (as returned by TO_CHAR (d, 'Day')) are unique, and NEXT_DAY accepts a two-character abbreviation. (In some other languages, abbreviations give less desirable results.)
• Anything after the required abbreviation is ignored, so the following are all valid and equivalent (in English):
• Sunday
• Su
• Sunny skies
• Sun 02-Nov-2003
• You don't have to worry about the trailing blanks that may come with TO_CHAR (d, 'Day')

As far as being quick, I wouldn't worry. The biggest bottlenecks in most applications are disk I/O and network traffic. This function uses little (if any) of either.

---

Footnote:

In languages where the name of one weekday (a) is itself an abbreviation of another (b) NEXT_DAY seems to regard any string LIKE 'a%' as a synonym for a, unless it is LIKE 'b%'. For example, when NLS_LANGUAGE = Turkish, 'Pazar' is Sunday and 'Pazartesi' is Monday. NEXT_DAY (d, x) returns the following Sunday when x is any of the following:

• Pazartes
• Pazarte
• Pazart

It makes no difference if NLS_TERRITORY = America (where Sunday is the first day of the week) or Turkey (where Monday is the first day).