Oracle SQL: the Essential Reference

NEXT_DAY

NEXT_DAY(d, string)

Computes the next occurrence of a specific weekday.

Parameters

d

Specifies a date value. Any time component in the date is preserved and is returned in the result.

string

Specifies the name of a weekday. This parameter may be a full name, such as Wednesday, or an abbreviation, such as Wed. Day names must be valid for your current NLS_DATE_LANGUAGE setting.

Example

SQL> SELECT NEXT_DAY('20-May-2000', 'SATURDAY'), 
  2         NEXT_DAY('20-May-2000', 'Sun')
  3  FROM dual;

NEXT_DAY('2 NEXT_DAY('2
----------- -----------
27-May-2000 21-May-2000

NEXT_DAY always looks forward. If the date you pass in as a parameter happens to fall on the day you are searching for, NEXT_DAY returns the subsequent occurrence of that day. In the example, 20-May-2000 happens to fall on a Saturday, yet the function returns 27-May-2000 as a result because that is the "next" Saturday. If you want NEXT_DAY to return your input date as a result, you should subtract 1 from it first. For example:

SQL> SELECT NEXT_DAY(TO_DATE('20-May-2000')-1, 'SATURDAY')
  2  FROM dual;

NEXT_DAY(TO
-----------
20-May-2000

The TO_DATE function was used in this second example because Oracle won't allow you to subtract 1 from a character string. Subtracting 1 from the date caused NEXT_DAY to return 20-May-2000, which happens to fall on a Saturday.