All Products
Search

This Product

    MaxCompute:DATEADD

    Document Center

    MaxCompute:DATEADD

    Last Updated:Jul 24, 2023

    Changes a date value based on the time unit specified by datepart and the interval specified by delta.

    Usage notes

    To add or subtract an interval to or from the current time, you can use this function with the GETDATE function.

    Syntax

    date|datetime dateadd(date|datetime|timestamp <date>, bigint <delta>, string <datepart>)

    Parameters

    • date: required. A date value of the DATE, DATETIME, or TIMESTAMP type.

      A value of the STRING type is implicitly converted into a value of the DATETIME type before calculation if the value format conforms to the DATETIME-type format yyyy-mm-dd hh:mi:ss, such as 2021-08-28 00:00:00, and the MaxCompute V1.0 data type edition is used.

    • delta: required. The interval that you want to add to or subtract from the specified component of a date value. The value of this parameter must be of the BIGINT type. If the value of delta is greater than 0, an interval is added to a date value. Otherwise, an interval is subtracted from a date value.

      If the input value is of the STRING or DOUBLE type, the value is implicitly converted into a value of the BIGINT type before calculation.

      Note

      • If you add or subtract the interval specified by delta based on the time unit specified by datepart, a carry or return at more significant date components may occur. The year, month, hour, minute, and second components are calculated by using different numeral systems. The year component uses the base-10 numeral system. The month component uses the base-12 numeral system. The hour component uses the base-24 numeral system. The minute and second components use the base-60 numeral system.

      • If the DATEADD function adds the interval specified by delta to the month component of a date value and this operation does not cause an overflow on the day component, you can retain the value of the day component. Otherwise, you must set the value of the day component to the last day of the specified month.

    • datepart: required. The date part that you want to modify in the date value. The value is a constant of the STRING type. If the format of the input value is invalid or the input value is not a constant of the STRING type, an error is returned.

      The value of this parameter is specified in compliance with the rules of conversions between the STRING and DATETIME types. The value yyyy indicates that the DATEADD function adds or subtracts an interval to the year component of the date value. The value mm indicates that the DATEADD function adds or subtracts an interval to the month component of the date value. The value dd indicates that the DATEADD function adds or subtracts an interval to the day component of the date value. For more information about the rules for type conversions, see Type conversions. The Extended Date/Time Format (EDTF) is also supported, such as -year, -month, -mon, -day, or -hour.

    Return value

    A value of the DATE or DATETIME type is returned. The return value is in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format. The return value varies based on the following rules:

    • If the value of date is not of the DATE, DATETIME, or TIMESTAMP type, an error is returned.

    • If the value of date is null, an error is returned.

    • If the value of delta or datepart is null, the return value is null.

    Sample data

    This section provides sample source data for you to understand how to use date functions. In this topic, a table named mf_date_fun_t is created and data is inserted into the table. Sample statements: 

    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);

    Query data from the mf_date_fun_t table. Sample statement:

    select * from mf_date_fun_t;
-- The following result is returned: 
+------+-------+------------+------------+-------+------------+------------+-------+------------+
| id   | date1 | datetime1  | timestamp1 | date2 | datetime2  | timestamp2 | date3 | date4      |
+------+-------+------------+------------+-------+------------+------------+-------+------------+
| 1    | 2021-11-29 | 2021-11-29 00:01:00 | 2021-01-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-20 | 123456780  |
| 2    | 2021-11-28 | 2021-11-28 00:02:00 | 2021-02-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-21 | 123456781  |
| 3    | 2021-11-27 | 2021-11-27 00:03:00 | 2021-03-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-22 | 123456782  |
| 4    | 2021-11-26 | 2021-11-26 00:04:00 | 2021-04-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-23 | 123456783  |
| 5    | 2021-11-25 | 2021-11-25 00:05:00 | 2021-05-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-24 | 123456784  |
| 6    | 2021-11-24 | 2021-11-24 00:06:00 | 2021-06-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-25 | 123456785  |
| 7    | 2021-11-23 | 2021-11-23 00:07:00 | 2021-07-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-26 | 123456786  |
| 8    | 2021-11-22 | 2021-11-22 00:08:00 | 2021-08-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-27 | 123456787  |
| 9    | 2021-11-21 | 2021-11-21 00:09:00 | 2021-09-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-28 | 123456788  |
| 10   | 2021-11-20 | 2021-11-20 00:10:00 | 2021-10-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-29 | 123456789  |
+------+-------+------------+------------+-------+------------+------------+-------+------------+

    Examples: static data

    • Example 1: common use. 

      -- The return value is 2005-03-01 00:00:00. After one day is added, the result is beyond the last day of February. The first day of March is returned. 
select dateadd(datetime '2005-02-28 00:00:00', 1, 'dd');
-- The return value is 2005-02-27 00:00:00. One day is subtracted. 
select dateadd(datetime '2005-02-28 00:00:00', -1, 'dd');
-- The return value is 2006-10-28 00:00:00. After 20 months are added, the month overflows, and the year value increases by 1. 
select dateadd(datetime '2005-02-28 00:00:00', 20, 'mm');
-- The return value is 2005-03-28 00:00:00. 
select dateadd(datetime '2005-02-28 00:00:00', 1, 'mm');
-- The return value is 2005-02-28 00:00:00. February in 2005 has only 28 days. Therefore, the last day of February is returned. 
select dateadd(datetime '2005-01-29 00:00:00', 1, 'mm');
-- The return value is 2005-02-28 00:00:00. 
select dateadd(datetime '2005-03-30 00:00:00', -1, 'mm');
-- The return value is 2005-03-18. 
select dateadd(date '2005-02-18', 1, 'mm');
-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement along with the SQL statement. The return value is 2005-03-18 00:00:00.0. 
set odps.sql.type.system.odps2=true;
select dateadd(timestamp '2005-02-18 00:00:00', 1, 'mm');
-- If the current time is 2020-11-17 16:31:44, the return value is 2020-11-16 16:31:44. 
select dateadd(getdate(),-1,'dd');
-- The return value is null. 
select dateadd(date '2005-02-18', 1, null);

    • Example 2: use of DATEADD in which a value of the DATETIME type is expressed as a constant.

      In MaxCompute SQL statements, a value of the DATETIME type cannot be directly expressed as a constant. The following statement uses an invalid expression of a value of the DATETIME type:

      select dateadd(2005-03-30 00:00:00, -1, 'mm');

      To describe a constant of the DATETIME type, use a valid expression of a value of the DATETIME type in the following statement:

      -- Explicitly convert a constant of the STRING type into the DATETIME type. The return value is 2005-02-28 00:00:00. 
select dateadd(cast("2005-03-30 00:00:00" as datetime), -1, 'mm');

    • Example 3: The input value is of the STRING type. 

      -- The input value is of the STRING type but does not conform to the DATETIME-type format. As a result, an error is returned. 
select dateadd('2021-08-27',1,'dd');
-- The input value is of the STRING type and conforms to the DATETIME-type format, and the MaxCompute V1.0 data type edition is used in your project. The return value is 2005-03-01 00:00:00. 
set odps.sql.type.system.odps2=false;
select dateadd('2005-02-28 00:00:00', 1, 'dd');

    Examples: table data

    Change date values in the date1, datetime1, and timestamp1 columns based on the time unit specified by datepart and the interval specified by delta. Data in Sample data is used in this example. Sample statements: 

    -- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement along with the SQL statement. 
set odps.sql.type.system.odps2=true;
select date1, dateadd(date1,1,'dd') as date1_dateadd, datetime1, dateadd(datetime1,1,'mm') as datetime1_dateadd, timestamp1, dateadd(timestamp1,-1,'yyyy') as timestamp1_dateadd from mf_date_fun_t;

    The following result is returned: 

    +------------+---------------+---------------------+---------------------+-------------------------------+-------------------------------+
| date1      | date1_dateadd | datetime1           | datetime1_dateadd   | timestamp1                    | timestamp1_dateadd            |
+------------+---------------+---------------------+---------------------+-------------------------------+-------------------------------+
| 2021-11-29 | 2021-11-30    | 2021-11-29 00:01:00 | 2021-12-29 00:01:00 | 2021-01-11 00:00:00.123456789 | 2020-01-11 00:00:00.123456789 |
| 2021-11-28 | 2021-11-29    | 2021-11-28 00:02:00 | 2021-12-28 00:02:00 | 2021-02-11 00:00:00.123456789 | 2020-02-11 00:00:00.123456789 |
| 2021-11-27 | 2021-11-28    | 2021-11-27 00:03:00 | 2021-12-27 00:03:00 | 2021-03-11 00:00:00.123456789 | 2020-03-11 00:00:00.123456789 |
| 2021-11-26 | 2021-11-27    | 2021-11-26 00:04:00 | 2021-12-26 00:04:00 | 2021-04-11 00:00:00.123456789 | 2020-04-11 00:00:00.123456789 |
| 2021-11-25 | 2021-11-26    | 2021-11-25 00:05:00 | 2021-12-25 00:05:00 | 2021-05-11 00:00:00.123456789 | 2020-05-11 00:00:00.123456789 |
| 2021-11-24 | 2021-11-25    | 2021-11-24 00:06:00 | 2021-12-24 00:06:00 | 2021-06-11 00:00:00.123456789 | 2020-06-11 00:00:00.123456789 |
| 2021-11-23 | 2021-11-24    | 2021-11-23 00:07:00 | 2021-12-23 00:07:00 | 2021-07-11 00:00:00.123456789 | 2020-07-11 00:00:00.123456789 |
| 2021-11-22 | 2021-11-23    | 2021-11-22 00:08:00 | 2021-12-22 00:08:00 | 2021-08-11 00:00:00.123456789 | 2020-08-11 00:00:00.123456789 |
| 2021-11-21 | 2021-11-22    | 2021-11-21 00:09:00 | 2021-12-21 00:09:00 | 2021-09-11 00:00:00.123456789 | 2020-09-11 00:00:00.123456789 |
| 2021-11-20 | 2021-11-21    | 2021-11-20 00:10:00 | 2021-12-20 00:10:00 | 2021-10-11 00:00:00.123456789 | 2020-10-11 00:00:00.123456789 |
+------------+---------------+---------------------+---------------------+-------------------------------+-------------------------------+

    Related functions

    DATEADD is a date function. For more information about functions related to date computing and conversion, see Date functions.