SQL Reference - NEXT

NEXT_DAY

NEXT_DAY is a date function in SQL that is used to return the first day of the specified day of the week that is later than a particular date. The returned date value will have the same hours, minutes, and seconds as the original date. This function is most often utilized in business applications such as payroll and attendance systems to calculate future dates.

NEXT_DAY(date, char)

date: This is the date from which the function will begin its search. It should be an expression that can be converted into a DATE datatype, typically found in the format ‘DD-MON-YY’, ‘DD-MON-YYYY’, ‘DD/MM/YY’, ‘DD/MM/YYYY’
char: This parameter represents the day of the week that the function should return. It should be a valid string expression that specifies the day (e.g., ‘MONDAY’, ‘TUESDAY’, etc.). The function will search forward from the supplied date to find the next occurrence of this day.

Example

SELECT NEXT_DAY(TO_DATE('01-JAN-2022', 'DD-MON-YYYY'), 'FRIDAY') FROM dual;

Output

07-JAN-22

Explanation

In the provided example, the NEXT_DAY function is used to find the date of the next specified day of the week after a given date. The date ‘01-JAN-2022’ is used as the starting point and the function returns ‘07-JAN-22’, which is the date of the next ‘FRIDAY’.

NEXT_DAY(start_date, target_day_of_week)

start_date: This parameter is the date from which the calculation is performed. It must be a valid date or expression that can be implicitly converted to a DATE or DATETIME type.
target_day_of_week: This parameter indicates the target day of the week. It is represented as either the full name of the day (“Monday”, “Tuesday”, etc.) or the abbreviations (“Mon”, “Tue”, etc.). The function will return the first date that comes after the start_date and falls on the specified target_day_of_week.

Example

SELECT NEXT_DAY('2022-09-05', 'MONDAY') NEXT_DAY_OF_WEEK FROM DUAL;

Output

NEXT_DAY_OF_WEEK
-----------------
2022-09-12

Explanation

The NEXT_DAY function is used to get the first day of the week that comes after a specified date. In the given example, it returns the next ‘MONDAY’ after the date ‘2022-09-05’. The output is ‘2022-09-12’ which means the next ‘MONDAY’ after ‘2022-09-05’ is ‘2022-09-12’.