NEXT_DAY

Syntax

NEXT_DAY
(
  value          IN date,
  weekday        IN text
)
RETURNS date;

NEXT_DAY
(
  value          IN date,
  weekday_index  IN integer
)
RETURNS date;

NEXT_DAY
(
  value          IN TIMESTAMP WITH TIME ZONE,
  weekday        IN text
)
RETURNS TIMESTAMP without time zone;

NEXT_DAY
(
  value          IN TIMESTAMP WITH TIME ZONE,
  weekday_index  IN integer
)
RETURNS TIMESTAMP without time zone;

Overview

The NEXT_DAY function returns the date of the first specific day of the week that occurs after the given date.

This function takes a string representing the day of the week (e.g., ‘Monday’, ‘Tue’, etc.) or a day index (1 to 7, where 1 is Sunday) as an argument and processes it.

For the TIMESTAMP WITH TIME ZONE type, it returns a timestamp value with the original time portion intact after calculating the date.

Parameters

Parameter	Description
value	date, timestamp type: a date string value to base on.
weekday	text type: A string representing the target day of the week to be searched. It follows Oracle's day of the week convention, and only the first three characters of the string are used. The first three characters of the string, regardless of case, are as follows "sun", "mon", "tue", "wed", "thu", "fri", "sat"
weekday_index	integer type: the index of the day of the week you want to find. Valid values are 1 through 7, where 1 is considered Sunday, 2 is considered Monday, ..., and 7 is considered Saturday.

Example

-- char type day example: returns the first Monday after '2023-05-15' 
SELECT oracle.NEXT_DAY('2023-05-15'::date, 'Monday');

  next_day  
------------
 2023-05-22
(1 row)

-- index based day example : returns the first Monday(index 2) after '2023-05-15' 
SELECT oracle.NEXT_DAY('2023-05-15'::date, 2);

  next_day  
------------
 2023-05-22
(1 row)

-- TIMESTAMP WITH TIME ZONE example (char):
SELECT oracle.NEXT_DAY('2023-05-15 14:30:00+09'::timestamptz, 'Friday');

      next_day       
---------------------
 2023-05-19 14:30:00
(1 row)

-- TIMESTAMP WITH TIME ZONE example (index):
SELECT oracle.NEXT_DAY('2023-05-15 14:30:00+09'::timestamptz, 6);

      next_day       
---------------------
 2023-05-19 14:30:00
(1 row)