I need to determine if a particular day of the week exists between two given dates. I can't
figure out an elegant way to do this, only comparing every DOW between the two dates to DOW in
question. Is there an Oracle DATE function that does this? I need this check to be as quick as
possible, as it is built into a form.
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):
- 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.
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:
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).
This was first published in October 2003