In Oracle Database, the NEXT_DAY() function returns the date of the first instance of the specified weekday that is later than the specified date.

Syntax

The syntax goes like this:

NEXT_DAY(date, char)

Where date is the date and char is the specified weekday.

The char argument must be a day of the week in the date language of the current session, 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 the valid abbreviation are ignored.

Example

Here’s an example:

SELECT NEXT_DAY('01-OCT-2035', 'FRIDAY') AS "Next Friday"
FROM DUAL;

Result:

   Next Friday 
______________ 
05-OCT-35     

Weekday Abbreviations

The above example uses the full weekday name. In the following example, I use the abbreviation instead:

SELECT NEXT_DAY('01-OCT-2035', 'FRI') AS "Next Friday"
FROM DUAL;

Result:

   Next Friday 
______________ 
05-OCT-35     

Either way, the result is the same.

In fact, as long as the first part of the argument contains a valid weekday abbreviation, the function will return the date of the following weekday that matches that abbreviation. Any characters immediately following the valid abbreviation are ignored. So, even if the whole argument isn’t actually a valid weekday name, as long as the first part is a valid weekday abbreviation, it will return a result for that weekday.

Examples:

SELECT 
    NEXT_DAY('01-OCT-2035','Monkey') AS "Next Monkey",
    NEXT_DAY('01-OCT-2035','Thumbnail') AS "Next Thumbnail",
    NEXT_DAY('01-OCT-2035','Friar Tuck') AS "Next Friar Tuck"
FROM DUAL;

Result:

   Next Monkey    Next Thumbnail    Next Friar Tuck 
______________ _________________ __________________ 
08-OCT-35      04-OCT-35         05-OCT-35         

Language Settings

As mentioned, the weekday or its abbreviation must be a day of the week in the date language of the current session.

Here’s what happens when I change my session’s language, then try to run the same example again:

ALTER SESSION SET NLS_LANGUAGE = 'Spanish';

SELECT NEXT_DAY('01-OCT-2035', 'FRIDAY') AS "Next Friday"
FROM DUAL;

Result:

Session alterado.


Error que empieza en la línea: 1 del comando :
SELECT NEXT_DAY('01-OCT-2035','FRIDAY') AS "Next Friday"
FROM DUAL
Informe de error -
ORA-01846: día de la semana no válido

In this case, my language is Spanish, and so we’d need to provide the weekday in Spanish:

ALTER SESSION SET NLS_LANGUAGE = 'Spanish';

SELECT NEXT_DAY('01-OCT-2035', 'viernes') AS "Next Friday"
FROM DUAL;

Result:

   Next Friday 
______________ 
05-OCT-35     

Null Arguments

If either argument is null, the result is null:

ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';
SET NULL 'null';

SELECT
    NEXT_DAY(null, 'FRIDAY') AS "1",
    NEXT_DAY('01-OCT-2035', null) AS "2"
FROM DUAL;

Result:

      1       2 
_______ _______ 
null    null   

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Missing Argument

Calling NEXT_DAY() with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT NEXT_DAY()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT NEXT_DAY()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: