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
