DATE_ADD命令格式、參數和傳回值 - MaxCompute

按照delta幅度增減startdate日期的天數。

注意事項

若您需要擷取在目前時間基礎上，指定變動幅度的日期，請參見GETDATE函數。
若您需要精確到時、分或秒更細的幅度增減，請參見DATEADD函數。
本命令與DATE_SUB的增減邏輯相反。

命令格式

DATE DATE_ADD(DATE|TIMESTAMP|STRING <startdate>, BIGINT <delta>)

參數說明

參數

是否必填

說明

startdate

是

起始日期值。支援DATE、DATETIME或STRING類型。

如果參數為STRING類型，且MaxCompute專案的資料類型版本是1.0，則會隱式轉換為DATE類型後參與運算，且STRING參數格式至少要包含'yyyy-mm-dd'。例如'2025-07-27'。

delta

是

修改幅度。BIGINT類型。如果delta大於0，則增；delta小於0，則減；delta等於0，不增不減。

傳回值說明

返回DATE類型，格式為yyyy-mm-dd。返回規則如下：

startdate非DATE、DATETIME或STRING類型時，返回報錯。
startdate值為NULL時，返回報錯。
delta值為NULL時，返回NULL。

樣本資料

為便於理解各函數的使用方法，本文為您提供來源資料，基於來源資料提供函數相關樣本。建立表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'2024-11-29',DATETIME'2024-11-29 00:01:00',TIMESTAMP'2024-01-11 00:00:00.123456789',DATE'2024-10-29',DATETIME'2024-10-29 00:00:00',TIMESTAMP'2024-10-11 00:00:00.123456789','2024-11-20',123456780),
(2,DATE'2024-11-28',DATETIME'2024-11-28 00:02:00',TIMESTAMP'2024-02-11 00:00:00.123456789',DATE'2024-10-29',DATETIME'2024-10-29 00:00:00',TIMESTAMP'2024-10-11 00:00:00.123456789','2024-11-21',123456781),
(3,DATE'2024-11-27',DATETIME'2024-11-27 00:03:00',TIMESTAMP'2024-03-11 00:00:00.123456789',DATE'2024-10-29',DATETIME'2024-10-29 00:00:00',TIMESTAMP'2024-10-11 00:00:00.123456789','2024-11-22',123456782),
(4,DATE'2024-11-26',DATETIME'2024-11-26 00:04:00',TIMESTAMP'2024-04-11 00:00:00.123456789',DATE'2024-10-29',DATETIME'2024-10-29 00:00:00',TIMESTAMP'2024-10-11 00:00:00.123456789','2024-11-23',123456783),
(5,DATE'2024-11-25',DATETIME'2024-11-25 00:05:00',TIMESTAMP'2024-05-11 00:00:00.123456789',DATE'2024-10-29',DATETIME'2024-10-29 00:00:00',TIMESTAMP'2024-10-11 00:00:00.123456789','2024-11-24',123456784),
(6,DATE'2024-11-24',DATETIME'2024-11-24 00:06:00',TIMESTAMP'2024-06-11 00:00:00.123456789',DATE'2024-10-29',DATETIME'2024-10-29 00:00:00',TIMESTAMP'2024-10-11 00:00:00.123456789','2024-11-25',123456785),
(7,DATE'2024-11-23',DATETIME'2024-11-23 00:07:00',TIMESTAMP'2024-07-11 00:00:00.123456789',DATE'2024-10-29',DATETIME'2024-10-29 00:00:00',TIMESTAMP'2024-10-11 00:00:00.123456789','2024-11-26',123456786),
(8,DATE'2024-11-22',DATETIME'2024-11-22 00:08:00',TIMESTAMP'2024-08-11 00:00:00.123456789',DATE'2024-10-29',DATETIME'2024-10-29 00:00:00',TIMESTAMP'2024-10-11 00:00:00.123456789','2024-11-27',123456787),
(9,DATE'2024-11-21',DATETIME'2024-11-21 00:09:00',TIMESTAMP'2024-09-11 00:00:00.123456789',DATE'2024-10-29',DATETIME'2024-10-29 00:00:00',TIMESTAMP'2024-10-11 00:00:00.123456789','2024-11-28',123456788),
(10,DATE'2024-11-20',DATETIME'2024-11-20 00:10:00',TIMESTAMP'2024-10-11 00:00:00.123456789',DATE'2024-10-29',DATETIME'2024-10-29 00:00:00',TIMESTAMP'2024-10-11 00:00:00.123456789','2024-11-29',123456789);

查詢表mf_date_fun_t中的資料，命令樣本如下：

SELECT * FROM mf_date_fun_t;

-- 返回結果。
+------------+------------+------------+------------+------------+------------+------------+------------+------------+
| id         | date1      | datetime1  | timestamp1 | date2      | datetime2  | timestamp2 | date3      | date4      | 
+------------+------------+------------+------------+------------+------------+------------+------------+------------+
| 1          | 2024-11-29 | 2024-11-29 00:01:00 | 2024-01-11 00:00:00.123456789 | 2024-10-29 | 2024-10-29 00:00:00 | 2024-10-11 00:00:00.123456789 | 2024-11-20 | 123456780  | 
| 2          | 2024-11-28 | 2024-11-28 00:02:00 | 2024-02-11 00:00:00.123456789 | 2024-10-29 | 2024-10-29 00:00:00 | 2024-10-11 00:00:00.123456789 | 2024-11-21 | 123456781  | 
| 3          | 2024-11-27 | 2024-11-27 00:03:00 | 2024-03-11 00:00:00.123456789 | 2024-10-29 | 2024-10-29 00:00:00 | 2024-10-11 00:00:00.123456789 | 2024-11-22 | 123456782  | 
| 4          | 2024-11-26 | 2024-11-26 00:04:00 | 2024-04-11 00:00:00.123456789 | 2024-10-29 | 2024-10-29 00:00:00 | 2024-10-11 00:00:00.123456789 | 2024-11-23 | 123456783  | 
| 5          | 2024-11-25 | 2024-11-25 00:05:00 | 2024-05-11 00:00:00.123456789 | 2024-10-29 | 2024-10-29 00:00:00 | 2024-10-11 00:00:00.123456789 | 2024-11-24 | 123456784  | 
| 6          | 2024-11-24 | 2024-11-24 00:06:00 | 2024-06-11 00:00:00.123456789 | 2024-10-29 | 2024-10-29 00:00:00 | 2024-10-11 00:00:00.123456789 | 2024-11-25 | 123456785  | 
| 7          | 2024-11-23 | 2024-11-23 00:07:00 | 2024-07-11 00:00:00.123456789 | 2024-10-29 | 2024-10-29 00:00:00 | 2024-10-11 00:00:00.123456789 | 2024-11-26 | 123456786  | 
| 8          | 2024-11-22 | 2024-11-22 00:08:00 | 2024-08-11 00:00:00.123456789 | 2024-10-29 | 2024-10-29 00:00:00 | 2024-10-11 00:00:00.123456789 | 2024-11-27 | 123456787  | 
| 9          | 2024-11-21 | 2024-11-21 00:09:00 | 2024-09-11 00:00:00.123456789 | 2024-10-29 | 2024-10-29 00:00:00 | 2024-10-11 00:00:00.123456789 | 2024-11-28 | 123456788  | 
| 10         | 2024-11-20 | 2024-11-20 00:10:00 | 2024-10-11 00:00:00.123456789 | 2024-10-29 | 2024-10-29 00:00:00 | 2024-10-11 00:00:00.123456789 | 2024-11-29 | 123456789  | 
+------------+------------+------------+------------+------------+------------+------------+------------+------------+

使用樣本：待用資料樣本

--返回2005-03-01。加1天，結果超出當年2月份的最後1天，實際值為下個月的第1天。
SELECT DATE_ADD(DATETIME '2005-02-28 00:00:00', 1);

--返回2005-02-27。減1天。
SELECT DATE_ADD(DATE '2005-02-28', -1);

--返回2005-03-20。
SET odps.sql.type.system.odps2=false; 
SELECT DATE_ADD('2005-02-28 00:00:00', 20);

--假設目前時間為2020-11-17 16:31:44，返回2020-11-16。
SELECT DATE_ADD(getdate(),-1);

--返回NULL。
SELECT DATE_ADD('2005-02-28 00:00:00', null);

使用樣本：表資料樣本

基於樣本資料，對date1、datetime1和timestamp1列按照幅度變化天數，命令樣本如下。

-- 開啟2.0新類型。此命令需要與SQL語句一起提交。
SET odps.sql.type.system.odps2=true;
SELECT date1, 
       DATE_ADD(date1, 1) AS date1_date_add, 
       datetime1, 
       DATE_ADD(datetime1, -1) AS datetime1_date_add, 
       timestamp1, 
       DATE_ADD(timestamp1, 0) AS timestamp1_date_add 
FROM mf_date_fun_t;

返回結果如下。

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

MaxCompute：DATE_ADD