NEXT_DAY

Return the next date strictly after the input that falls on the specified weekday.

Category: datetimeReturns: DATEDialect: Standard

Syntax

NEXT_DAY(date, day_of_week)

Description

## Overview Returns the first date strictly after the input that falls on the specified weekday. 'Strictly after' means if the input is already on the target weekday, the result is the same weekday one week later, not the input itself. Use NEXT_DAY for scheduling recurring tasks, rolling weekend dates forward to a business day, or computing the next occurrence of a calendar event. ## Behavior - Returns a DATE. - Returns NULL if either argument is NULL. - The result is strictly greater than the input: always advances by at least one day, and up to seven days. - The weekday name is matched case-insensitively. - Raises an error if the weekday name is not recognized. ## Compatibility - Matches the traditional NEXT_DAY convention used widely in analytical SQL engines: strict-advance semantics (never returns the input date).

Parameters

Name	Type	Description
`date`		Specifies the starting date. The function searches forward from the day after this date.
`day_of_week`		Specifies the target weekday name. Accepts 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'. Matching is case-insensitive. Abbreviations (for example 'Mon') are accepted in some dialects.

Examples

-- Next Monday strictly after March 15 2025 (which is a Saturday)
SELECT NEXT_DAY(DATE '2025-03-15', 'Monday') AS next_mon;

-- Next Friday after today
SELECT NEXT_DAY(CURRENT_DATE(), 'Friday') AS next_fri;

-- Compute the next business-day reminder for each task
SELECT task_id, NEXT_DAY(completed_date, 'Wednesday') AS next_due
FROM project_mgmt.core.tasks;

-- If the input is already the target weekday, the result advances to the following week
SELECT NEXT_DAY(DATE '2025-03-17', 'Monday') AS result; -- returns 2025-03-24

-- Roll orders placed on weekends to the next Monday
SELECT order_id,
       CASE WHEN DAYOFWEEK(order_date) IN (1, 7)
            THEN NEXT_DAY(order_date, 'Monday')
            ELSE order_date END AS effective_date
FROM commerce.sales.orders;

Pitfalls

NEXT_DAY is strictly advancing. NEXT_DAY(DATE '2025-03-17', 'Monday') returns 2025-03-24, not 2025-03-17, even though the input is already a Monday. For inclusive matching add a CASE that returns the input when DAYOFWEEK matches.
Weekday strings are matched by name in English. Non-English names or unexpected abbreviations raise errors rather than silently matching.
There is no corresponding PREVIOUS_DAY in most dialects. To find the previous occurrence of a weekday, compute (NEXT_DAY(date, weekday) - INTERVAL '7 days') or build an explicit DAYOFWEEK-based offset.
The function operates on DATE, not TIMESTAMP. Time-of-day information in TIMESTAMP inputs is discarded.