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.
There
Requires Free Membership to View
is an Oracle function, NEXT_DAY, that you can use in a fairly elegant solution.
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).
This was first published in October 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation