Problem solve Get help with specific problems with your technologies, process and projects.

Checking the day of the week

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 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.


    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).

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.