Oracle NEXT_DAY Function: A Simple Guide

The NEXT_DAY function in Oracle SQL is a handy date function that helps you find the date of the next specified day of the week.

For example, if it's a Wednesday, you can use NEXT_DAY to find the date of the upcoming Friday.

What is the NEXT_DAY Function in Oracle?

The NEXT_DAY(date, char) function returns the date of the first weekday (specified by char) that is later than the given date.

This is the most important rule to understand: "later than". If you run the function on a Monday and ask for the next 'MONDAY', it will return the date for next Monday (7 days later), not the current date.

The function returns a DATE value, and it keeps the same time component (hours, minutes, seconds) as the input date.

NEXT_DAY Function Syntax

The syntax for NEXT_DAY is:

NEXT_DAY(date, char)

Let's break that down:

• date: The starting date. The function will find the next specified weekday after this date.
• char: The day of the week you are looking for. This must be a string (like 'MONDAY' or 'MON') in the date language of your session (e.g., 'TUESDAY' for English, 'MARDI' for French).

A Note on Viewing Results

To see the full date and time in your results, you may want to run this command first:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Oracle NEXT_DAY Function Examples

Here are two practical examples of how to use NEXT_DAY.

Example 1: Finding the Next Friday with NEXT_DAY

Let's say we have a date, 12-NOV-2025 (which is a Wednesday), and we want to find the date of the next Friday.

Query:

SELECT 
  NEXT_DAY(DATE '2025-11-12', 'FRIDAY') AS "Next_Friday"
FROM DUAL;

Result: (The next Friday after Wednesday, Nov 12th is Nov 14th.)

Next_Friday
--------------------
14-NOV-2025 00:00:00

Example 2: Understanding the "Later Than" Rule using NEXT_DAY

This example shows what happens when the start date is the same as the day you are looking for.

10-NOV-2025 is a Monday. If we ask for the NEXT_DAY that is a 'MONDAY', it will return the date for the following Monday, not the same day.

Query:

SELECT 
  NEXT_DAY(DATE '2025-11-10', 'MONDAY') AS "Following_Monday"
FROM DUAL;

Result: (The result is 7 days later.)

Following_Monday
--------------------
17-NOV-2025 00:00:00