Adds or subtracts a number of days that is specified by delta to or from a date value that is specified by startdate.
Usage notes
To add or subtract a number of days to or from the current time, you can use this function with the GETDATE function.
The logic of this function is opposite to that of the DATE_ADD function.
Syntax
date date_sub(date|timestamp|string <startdate>, bigint <delta>)Parameters
- startdate: required. The start date. A value of the DATE, DATETIME, or STRING type is supported.
If the input value is of the STRING type and the MaxCompute V1.0 data type edition is used in your project, the input value is implicitly converted into the DATE type before calculation. The input value of the STRING type must include at least the
'yyyy-mm-dd'part, such as'2019-12-27'. delta: required. The number of days that you want to add or subtract. The value of this parameter must be of the BIGINT type. If the value of delta is greater than 0, a number of days are subtracted from the start date. If the value of delta is less than 0, a number of days are added to the start date. If the value of delta is 0, the date value remains unchanged.
Return value
A value of the DATE type is returned. The return value is in the yyyy-mm-dd format. The return value varies based on the following rules:
If the value of startdate is not of the DATE, DATETIME, or STRING type, an error is returned.
If the value of startdate is null, an error is returned.
If the value of delta is null, null is returned.
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 |
+------+-------+------------+------------+-------+------------+------------+-------+------------+Example: static data
-- Enable the MaxCompute V2.0 data type edition. Commit this command along with the SQL statement.
set odps.sql.type.system.odps2=true;
-- The return value is 2005-02-28. One day is subtracted. The last day of February is returned.
select date_sub(datetime '2005-03-01 00:00:00', 1);
-- The return value is 2005-03-01. One day is added.
select date_sub(date '2005-02-28', -1);
-- The return value is 2005-02-27. Two days are subtracted.
set odps.sql.type.system.odps2=false;
select date_sub('2005-03-01 00:00:00', 2);
-- If the current time is 2021-09-10 16:31:44, the return value is 2021-09-09.
select date_sub(getdate(),1);
-- The return value is null.
select date_sub('2005-03-01 00:00:00', null);Example: table data
Change date values in the date1, datetime1, and timestamp1 columns based on the number of days specified by delta. Data in Sample data is used in this example. Sample statement:
-- Enable the MaxCompute V2.0 data type edition. Commit this command along 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;The following result is returned:
+------------+----------------+---------------------+--------------------+-------------------------------+---------------------+
| 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_SUB is a date function. For more information about functions related to date computing and conversion, see Date functions.