DocsICP FilingConsole
Documentation
Home Page MaxCompute Developer Reference Development reference: SQL Function reference Built-in functions Date and Time Functions NEXT_DAY

NEXT_DAY

Updated at:
Copy as MD
Product
My Favorites

Returns the date of the first occurrence of a specified day of the week that falls after a given start date.

NEXT_DAY is an additional function of MaxCompute V2.0. To use it with DATE, DATETIME, or TIMESTAMP input types, enable the MaxCompute V2.0 data type edition first.

Syntax

string next_day(timestamp|date|datetime|string <startdate>, string <week>)

Parameters

ParameterRequiredTypeDescription
startdateYesTIMESTAMP, DATE, DATETIME, or STRINGThe reference date. Accepted formats: yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss.ff3. If the value is STRING, it must include at least the yyyy-mm-dd part and must not contain extra characters.
weekYesSTRINGThe target day of the week. Case-insensitive. Valid values:

Valid values for week:

'SU', 'SUN', 'SUNDAY'
'MO', 'MON', 'MONDAY'
'TU', 'TUE', 'TUESDAY'
'WE', 'WED', 'WEDNESDAY'
'TH', 'THU', 'THURSDAY'
'FR', 'FRI', 'FRIDAY'
'SA', 'SAT', 'SATURDAY'

Return value

Returns a STRING value in yyyy-mm-dd format.

ConditionReturn value
startdate is not TIMESTAMP, DATE, DATETIME, or STRING, or the format does not meet requirementsnull
startdate is nullError
week is nullnull

Usage notes

Before calling NEXT_DAY with DATE, DATETIME, or TIMESTAMP input types, enable the MaxCompute V2.0 data type edition:

set odps.sql.type.system.odps2=true;

Submit this command together with your SQL statement in the same session.

Examples

Static examples

-- Returns 2017-08-08
select next_day('2017-08-01', 'TU');

-- Returns 2017-08-08
select next_day('2017-08-01 23:34:00', 'TU');

-- Returns null (format is invalid: no hyphens)
select next_day('20170801', 'TU');

-- Returns null (week is null)
select next_day('2017-08-01 23:34:00', null);

Table-based example

This example uses the sample table mf_date_fun_t. For the table definition and data, see Sample data.

-- Enable the MaxCompute V2.0 data type edition. Submit this command with the SQL statement.
set odps.sql.type.system.odps2=true;
select date1,
       next_day(date1, 'MON')       as date1_next_day,
       datetime1,
       next_day(datetime1, 'TUE')   as datetime1_next_day,
       timestamp1,
       next_day(timestamp1, 'WED')  as timestamp1_next_day,
       date3,
       next_day(date3, 'THU')       as date3_next_day
from mf_date_fun_t;

Result:

+------------+----------------+---------------------+--------------------+-------------------------------+---------------------+------------+----------------+
| date1      | date1_next_day | datetime1           | datetime1_next_day | timestamp1                    | timestamp1_next_day | date3      | date3_next_day |
+------------+----------------+---------------------+--------------------+-------------------------------+---------------------+------------+----------------+
| 2021-11-29 | 2021-12-06     | 2021-11-29 00:01:00 | 2021-11-30         | 2021-01-11 00:00:00.123456789 | 2021-01-13          | 2021-11-20 | 2021-11-25     |
| 2021-11-28 | 2021-11-29     | 2021-11-28 00:02:00 | 2021-11-30         | 2021-02-11 00:00:00.123456789 | 2021-02-17          | 2021-11-21 | 2021-11-25     |
| 2021-11-27 | 2021-11-29     | 2021-11-27 00:03:00 | 2021-11-30         | 2021-03-11 00:00:00.123456789 | 2021-03-17          | 2021-11-22 | 2021-11-25     |
| 2021-11-26 | 2021-11-29     | 2021-11-26 00:04:00 | 2021-11-30         | 2021-04-11 00:00:00.123456789 | 2021-04-14          | 2021-11-23 | 2021-11-25     |
| 2021-11-25 | 2021-11-29     | 2021-11-25 00:05:00 | 2021-11-30         | 2021-05-11 00:00:00.123456789 | 2021-05-12          | 2021-11-24 | 2021-11-25     |
| 2021-11-24 | 2021-11-29     | 2021-11-24 00:06:00 | 2021-11-30         | 2021-06-11 00:00:00.123456789 | 2021-06-16          | 2021-11-25 | 2021-12-02     |
| 2021-11-23 | 2021-11-29     | 2021-11-23 00:07:00 | 2021-11-30         | 2021-07-11 00:00:00.123456789 | 2021-07-14          | 2021-11-26 | 2021-12-02     |
| 2021-11-22 | 2021-11-29     | 2021-11-22 00:08:00 | 2021-11-23         | 2021-08-11 00:00:00.123456789 | 2021-08-18          | 2021-11-27 | 2021-12-02     |
| 2021-11-21 | 2021-11-22     | 2021-11-21 00:09:00 | 2021-11-23         | 2021-09-11 00:00:00.123456789 | 2021-09-15          | 2021-11-28 | 2021-12-02     |
| 2021-11-20 | 2021-11-22     | 2021-11-20 00:10:00 | 2021-11-23         | 2021-10-11 00:00:00.123456789 | 2021-10-13          | 2021-11-29 | 2021-12-02     |
+------------+----------------+---------------------+--------------------+-------------------------------+---------------------+------------+----------------+

Sample data

The examples above use the following table. Run these statements to create and populate mf_date_fun_t:

create table if not exists mf_date_fun_t(
    id         int,
    date1      date,
    datetime1  datetime,
    timestamp1 timestamp,
    date2      date,
    datetime2  datetime,
    timestamp2 timestamp,
    date3      string,
    date4      bigint);

insert into mf_date_fun_t values
(1, DATE'2021-11-29', DATETIME'2021-11-29 00:01:00', TIMESTAMP'2021-01-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-20', 123456780),
(2, DATE'2021-11-28', DATETIME'2021-11-28 00:02:00', TIMESTAMP'2021-02-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-21', 123456781),
(3, DATE'2021-11-27', DATETIME'2021-11-27 00:03:00', TIMESTAMP'2021-03-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-22', 123456782),
(4, DATE'2021-11-26', DATETIME'2021-11-26 00:04:00', TIMESTAMP'2021-04-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-23', 123456783),
(5, DATE'2021-11-25', DATETIME'2021-11-25 00:05:00', TIMESTAMP'2021-05-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-24', 123456784),
(6, DATE'2021-11-24', DATETIME'2021-11-24 00:06:00', TIMESTAMP'2021-06-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-25', 123456785),
(7, DATE'2021-11-23', DATETIME'2021-11-23 00:07:00', TIMESTAMP'2021-07-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-26', 123456786),
(8, DATE'2021-11-22', DATETIME'2021-11-22 00:08:00', TIMESTAMP'2021-08-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-27', 123456787),
(9, DATE'2021-11-21', DATETIME'2021-11-21 00:09:00', TIMESTAMP'2021-09-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-28', 123456788),
(10, DATE'2021-11-20', DATETIME'2021-11-20 00:10:00', TIMESTAMP'2021-10-11 00:00:00.123456789', DATE'2021-10-29', DATETIME'2021-10-29 00:00:00', TIMESTAMP'2021-10-11 00:00:00.123456789', '2021-11-29', 123456789);

Related functions

For other date computing and conversion functions, see Date functions.

Previous：MONTHS_BETWEENNext：NOW