Oracle NEXT_DAY() Function

Oracle NEXT_DAY() is a built-in function that returns the date of the specified first workday that occurs after a given date.

Oracle NEXT_DAY() Syntax

Here’s the syntax for the Oracle NEXT_DAY() function:

NEXT_DAY(date, char)

Parameters

date

Required.

char

Required. It must be the name of a day of the week in the session date language, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following a valid abbreviation are ignored.

Return Value

The Oracle NEXT_DAY() function returns the date of the specified first workday that occurs after a given date.

The return value of the function has the same hour, minute, and second as the date parameter. The return value type is always DATE, regardless of the type of the date parameter.

If either argument is NULL, NEXT_DAY() returns NULL.

Oracle NEXT_DAY() Examples

Here are some examples that demonstrate the usage of the Oracle NEXT_DAY() function.

Basic Usage

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
ALTER SESSION SET NLS_LANGUAGE = 'English';
SELECT
  NEXT_DAY('2023-02-12', 'Tuesday')
FROM dual;

Output:

NEXT_DAY('2023-02-12','TUESDAY')
___________________________________
2023-02-14

In this example, the session language is set to English, and the function is passed Tuesday. Of course, you could use the abbreviation Tue for Tuesday:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
ALTER SESSION SET NLS_LANGUAGE = 'English';
SELECT
  NEXT_DAY('2023-02-12', 'Tue')
FROM dual;

Output:

NEXT_DAY('2023-02-12','TUE')
_______________________________
2023-02-14

Language Settings

The second argument depends on the language used in the current session. Suppose you are using Simplified Chinese. You can use the following statement:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
ALTER SESSION SET NLS_LANGUAGE = 'SIMPLIFIED CHINESE';
SELECT
  NEXT_DAY('2023-02-12', '星期二')
FROM dual;

Output:

NEXT_DAY('2023-02-12','星期二')
_______________________________
2023-02-14

NULL Parameters

If either argument is NULL, NEXT_DAY() returns NULL.

ALTER SESSION SET NLS_LANGUAGE = 'English';
SET NULL 'NULL';
SELECT
    NEXT_DAY(NULL, 'Tue') NULL_1,
    NEXT_DAY('2023-02-12', NULL) NULL_2,
    NEXT_DAY(NULL, NULL) NULL_3
FROM dual;

Output:

NULL_1    NULL_2    NULL_3
_________ _________ _________
NULL      NULL      NULL

In this example, the SET NULL 'NULL'; statement is used to display NULL values as the string 'NULL'.

Conclusion

Oracle NEXT_DAY() is a built-in function that returns the date of the specified first workday that occurs after a given date.