MaxCompute:DATEADD

Usage notes

You can use this function with the GETDATE function to obtain a date by adding a specific interval to the current time.

Syntax

DATEADD(DATE|DATETIME|TIMESTAMP|TIMESTAMP_NTZ <date>, BIGINT <delta>, STRING <datepart>)

Parameters

• date: Required. The date or timestamp value. This parameter supports the DATE, DATETIME, TIMESTAMP, and TIMESTAMP_NTZ data types.

  In the MaxCompute 1.0 data type edition, if this parameter is a STRING in the yyyy-mm-dd hh:mi:ss format, such as 2025-07-21 00:00:00, it is implicitly converted to the DATETIME type.

• delta: Required. The interval that you want to add. This parameter is a BIGINT. If the value of delta is greater than 0, the interval is added. Otherwise, the interval is subtracted.

  In the MaxCompute 1.0 data type edition, if this parameter is a STRING or DOUBLE value, it is implicitly converted to the BIGINT type.

  Note

  • When you add or subtract the interval specified by delta, a carry or borrow may occur in a more significant time unit. The year, month, hour, minute, and second components are calculated using the base-10, base-12, base-24, and base-60 numeral systems, respectively.

  • When the unit for delta is month, if adding the delta value for the delta interval does not cause a Day overflow, the Day value remains unchanged. Otherwise, the Day value is set to the last day of the resulting month.

• datepart: Required. The time unit of the interval. This parameter is a STRING constant. If you specify a non-constant value, an unsupported format, or another data type, an error is returned. The following values are supported:

  Time unit	Value
  Year	year or yyyy
  Quarter	quarter or q
  Month	month, mon, or mm
  Week	week
  Day	day or dd
  Hour	hour or hh
  Minute	mi
  Second	ss
  Millisecond	ff3
  Microsecond	ff6

Return value

Examples

• Example 1: Common usage of static data

  -- The return value is 2005-03-01 00:00:00. One day is added. The result exceeds the last day of February of that year. The actual value is the first day of the next month.
  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. 20 months are added. The month overflows, and the year 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 does not have the 29th day. The date is truncated to the last day of the month.
  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-05-28.
  SELECT DATEADD(DATE '2005-02-28', 1, 'quarter');
  -- The return value is 2005-03-18.
  SELECT DATEADD(DATE '2005-02-18', 1, 'mm');
  -- The return value is 2005-03-14 00:00:00.
  SELECT DATEADD(DATETIME '2005-02-28 00:00:00', 2, 'week');
  -- The return value is 2005-03-14 00:00:00.123.
  SELECT DATEADD(TIMESTAMP '2005-02-28 00:00:00.123', 14, 'day');
  -- The return value is 2005-03-30 02:24:00. 3 hours are subtracted. 
  SELECT DATEADD(DATETIME '2005-03-30 05:24:00', -3, 'hh');
  -- The return value is 2005-03-30 04:54:00. 30 minutes are subtracted. 
  SELECT DATEADD(DATETIME '2005-03-30 05:24:00', -30, 'mi');
  -- The return value is 2005-03-30 05:23:30. 30 seconds are subtracted. 
  SELECT DATEADD(DATETIME '2005-03-30 05:24:00', -30, 'ss');
  -- The return value is 2005-02-28 00:00:01.123456.
  SELECT DATEADD(TIMESTAMP_NTZ '2005-02-28 00:00:00.123456', 1000, 'ff3');
  -- The return value is 2005-02-28 00:00:00.124456.
  SELECT DATEADD(TIMESTAMP '2005-02-28 00:00:00.123456', 1000, 'ff6');
  
  -- Enable the MaxCompute 2.0 data type edition. This command must be submitted together 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');
  -- Assume that 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');
  -- NULL is returned.
  SELECT DATEADD(DATE '2005-02-18', 1, NULL);

• Example 2: Description of the date parameter type

  • For the MaxCompute 2.0 data type edition, if the data type of the date parameter is not DATE, DATETIME, TIMESTAMP, or TIMESTAMP_NTZ, you must convert the data type. Otherwise, an error is returned.

    SELECT DATEADD(CAST('2005-03-30 00:00:00' AS datetime), -1, 'mm');
    
    -- The following result is returned.
    +------------+
    | _c0        | 
    +------------+
    | 2005-02-28 00:00:00 | 
    +------------+

  • For the MaxCompute 1.0 data type edition, if the date parameter is a STRING in the yyyy-mm-dd hh:mi:ss format, it is implicitly converted to the DATETIME type.

    -- Set the data type edition to 1.0.
    SET odps.sql.type.system.odps2=false;
    SET odps.sql.hive.compatible=false;
    
    -- Incorrect example: The following input parameter is of the STRING type but does not conform to the DATETIME format. NULL is returned.
    SELECT DATEADD('2021-08-27',1,'dd');
    
    -- Correct example: The following input parameter is of the STRING type and conforms to the DATETIME format.
    SELECT DATEADD('2005-02-28 00:00:00', 1000000000, 'ff6');
    
    -- The following result is returned.
    +------------+
    | _c0        | 
    +------------+
    | 2005-02-28 00:16:40 | 
    +------------+

Related functions

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