Oracle NEXT_DAY Function

The Oracle NEXT_DAY() function returns the date of the first weekday specified by day name that is later than a date.

Syntax #

The following shows the syntax of the syntax of the NEXT_DAY() function:

NEXT_DAY(date, weekday)Code language: SQL (Structured Query Language) (sql)

Arguments #

The NEXT_DAY() function accepts two arguments:

date is a DATE value or an expression that evaluates to a DATE value which is used to find the next weekday.
weekday is the day of the week that you want to return.

The weekday can be full name e.g., Tuesday or abbreviation e.g., Tue. In addition, the language of the weekday must be in the language of your session.

In English, the weekday accepts the following values:

Weekday	Abbr.	Return
SUNDAY	SUN	The first Sunday later than a date
MONDAY	MON	The first Monday later than a date
TUESDAY	TUE	The first Tuesday later than a date
WEDNESDAY	WED	The first Wednesday later than a date
THURSDAY	THUR	The first Thursday later than a date
FRIDAY	FRI	The first Friday later than a date
SATURDAY	SAT	The first Saturday later than a date

Return value #

The Oracle NEXT_DAY() function always returns a DATE value that represents the next weekday after the date.

The result date has the same hours, minutes, and seconds as the input date.

Examples #

Getting the next weekday later than a date #

This example uses the NEXT_DAY function to return the date of the next Sunday after January 1, 2000:

SELECT
  NEXT_DAY( DATE '2000-01-01', 'SUNDAY' )
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

It returns the following output:

02-JAN-00Code language: SQL (Structured Query Language) (sql)

Getting the next working day for employees #

See the following employees table in the sample database:

The following statement uses the NEXT_DAY function to find the next working days which are later than the hire dates for the employees:

SELECT
  first_name, 
  last_name, 
  hire_date, 
  NEXT_DAY( hire_date, 'Monday' )  NEXT_MONDAY
FROM
  employees
ORDER BY
  hire_date DESC; Code language: SQL (Structured Query Language) (sql)

Output:

In this example, the next working day of an employee is the next Monday after the hire date.

Summary #

Use the Oracle NEXT_DAY() function to get the date of the next weekday which is later than a specified date.

Was this tutorial helpful?