All Products
Search

This Product

    MaxCompute:DATE_SUB

    Document Center

    MaxCompute:DATE_SUB

    Last Updated:Mar 26, 2026

    Returns the date that is delta days before startdate. Use a negative delta to get a date after startdate.

    Usage notes

    • DATE_SUB is the opposite of DATE_ADD: a positive delta subtracts days, and a negative delta adds days.

    • To subtract days from the current date, combine DATE_SUB with GETDATE.

    • When startdate is a DATETIME or TIMESTAMP value, the time component is discarded. The return value is always a DATE in yyyy-mm-dd format.

    Syntax

    date date_sub(date|timestamp|string <startdate>, bigint <delta>)

    Parameters

    ParameterRequiredTypeDescription
    startdateYesDATE, DATETIME, or STRINGThe start date. If the MaxCompute V1.0 data type edition is used and the value is of the STRING type, it is implicitly converted to DATE before calculation. The STRING value must include at least the 'yyyy-mm-dd' portion, for example, '2019-12-27'.
    deltaYesBIGINTThe number of days to shift. Positive values subtract days from startdate. Negative values add days to startdate. A value of 0 leaves the date unchanged.

    Return value

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

    ConditionResult
    delta is nullReturns null
    startdate is nullReturns an error
    startdate is not a DATE, DATETIME, or STRING valueReturns an error
    When startdate is a DATETIME or TIMESTAMP value, the time component is truncated. Only the date portion is returned.

    Sample data

    The examples in this topic use the mf_date_fun_t table. Run the following statements to create and populate the table:

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

    To view the table contents, run:

    select * from mf_date_fun_t;
-- Result:
+------+------------+---------------------+-------------------------------+------------+---------------------+-------------------------------+------------+------------+
| 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

    Subtract days from a date

    -- Enable the MaxCompute V2.0 data type edition. Run this command together with the SQL statement.
set odps.sql.type.system.odps2=true;
-- Returns 2005-02-28: subtracts 1 day from 2005-03-01. The result is the last day of February.
select date_sub(datetime '2005-03-01 00:00:00', 1);
-- Returns 2005-02-27: subtracts 2 days from 2005-03-01 (MaxCompute V1.0 data type edition).
set odps.sql.type.system.odps2=false;
select date_sub('2005-03-01 00:00:00', 2);

    Add days using a negative delta

    -- Enable the MaxCompute V2.0 data type edition. Run this command together with the SQL statement.
set odps.sql.type.system.odps2=true;
-- Returns 2005-03-01: adds 1 day to 2005-02-28.
select date_sub(date '2005-02-28', -1);

    Use with GETDATE to calculate a date relative to today

    -- If the current time is 2021-09-10 16:31:44, returns 2021-09-09.
select date_sub(getdate(), 1);

    Handle a null delta

    -- Returns null.
select date_sub('2005-03-01 00:00:00', null);

    Apply to table columns

    Subtract or add days from the date1, datetime1, and timestamp1 columns in the mf_date_fun_t table:

    -- Enable the MaxCompute V2.0 data type edition. Run this command together with the SQL statement.
set odps.sql.type.system.odps2=true;
select
  date1,
  date_sub(date1, 1)      as date1_date_sub,
  datetime1,
  date_sub(datetime1, -1) as datetime1_date_sub,
  timestamp1,
  date_sub(timestamp1, 0) as timestamp1_date_sub
from mf_date_fun_t;

    Result:

    +------------+----------------+---------------------+--------------------+-------------------------------+---------------------+
| date1      | date1_date_sub | datetime1           | datetime1_date_sub | timestamp1                    | timestamp1_date_sub |
+------------+----------------+---------------------+--------------------+-------------------------------+---------------------+
| 2021-11-29 | 2021-11-28     | 2021-11-29 00:01:00 | 2021-11-30         | 2021-01-11 00:00:00.123456789 | 2021-01-11          |
| 2021-11-28 | 2021-11-27     | 2021-11-28 00:02:00 | 2021-11-29         | 2021-02-11 00:00:00.123456789 | 2021-02-11          |
| 2021-11-27 | 2021-11-26     | 2021-11-27 00:03:00 | 2021-11-28         | 2021-03-11 00:00:00.123456789 | 2021-03-11          |
| 2021-11-26 | 2021-11-25     | 2021-11-26 00:04:00 | 2021-11-27         | 2021-04-11 00:00:00.123456789 | 2021-04-11          |
| 2021-11-25 | 2021-11-24     | 2021-11-25 00:05:00 | 2021-11-26         | 2021-05-11 00:00:00.123456789 | 2021-05-11          |
| 2021-11-24 | 2021-11-23     | 2021-11-24 00:06:00 | 2021-11-25         | 2021-06-11 00:00:00.123456789 | 2021-06-11          |
| 2021-11-23 | 2021-11-22     | 2021-11-23 00:07:00 | 2021-11-24         | 2021-07-11 00:00:00.123456789 | 2021-07-11          |
| 2021-11-22 | 2021-11-21     | 2021-11-22 00:08:00 | 2021-11-23         | 2021-08-11 00:00:00.123456789 | 2021-08-11          |
| 2021-11-21 | 2021-11-20     | 2021-11-21 00:09:00 | 2021-11-22         | 2021-09-11 00:00:00.123456789 | 2021-09-11          |
| 2021-11-20 | 2021-11-19     | 2021-11-20 00:10:00 | 2021-11-21         | 2021-10-11 00:00:00.123456789 | 2021-10-11          |
+------------+----------------+---------------------+--------------------+-------------------------------+---------------------+

    Related functions

    • DATE_ADD: Adds a specified number of days to a date. The logic is the opposite of DATE_SUB.

    • GETDATE: Returns the current date and time. Combine with DATE_SUB to calculate dates relative to today.

    • Date functions: A complete reference for date computing and conversion functions in MaxCompute.