DATEADD命令格式、参数和返回值_云原生大数据计算服务 MaxCompute(MaxCompute)-阿里云帮助中心

按照指定的单位datepart和幅度delta修改date（日期）的值。

注意事项

如果您需要获取在当前时间基础上指定变动幅度的日期，请结合GETDATE函数使用。

命令格式

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

参数说明

date：必填。日期值，DATE、DATETIME或TIMESTAMP类型。
如果参数为STRING类型，格式符合DATETIME类型的格式，即yyyy-mm-dd hh:mi:ss，例如2021-08-28 00:00:00，且MaxCompute项目的数据类型版本是1.0，则会隐式转换为DATETIME类型后参与运算。
delta：必填。修改幅度，BIGINT类型。如果delta大于0，则增，否则减。
如果参数为STRING或DOUBLE类型，则会隐式转换为BIGINT类型后参与运算。
说明
- 按照指定的单位增减delta时，会导致更高单位的进位或退位。年、月、时、分秒分别按照10进制、12进制、24进制、60进制进行计算。
- 当delta的单位是月时，如果delta的月部分在增加delta值之后不造成Day溢出，则保持Day值不变，否则将Day值设置为结果月份的最后1天。
datepart：必填。指定修改的单位，STRING类型常量。非常量、不支持的格式或其他类型会返回报错。
此字段的取值遵循STRING与DATETIME类型转换的约定，即yyyy表示年，mm表示月，dd表示天。关于类型转换的规则，详情请参见数据类型转换。该字段也支持扩展的日期格式：年-year、月-month或-mon、日-day和小时-hour。

返回值说明

返回DATE或DATETIME类型，格式为yyyy-mm-dd或yyyy-mm-dd hh:mi:ss。返回规则如下：

date非DATE、DATETIME或TIMESTAMP类型时，返回报错。
date值为NULL时，返回报错。
delta或datepart值为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'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);

查询表mf_date_fun_t中的数据，命令示例如下：

select * from mf_date_fun_t;
--返回结果。
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+
| 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.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-20 | 123456780  |
| 2          | 2021-11-28 | 2021-11-28 00:02:00 | 2021-02-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-21 | 123456781  |
| 3          | 2021-11-27 | 2021-11-27 00:03:00 | 2021-03-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-22 | 123456782  |
| 4          | 2021-11-26 | 2021-11-26 00:04:00 | 2021-04-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-23 | 123456783  |
| 5          | 2021-11-25 | 2021-11-25 00:05:00 | 2021-05-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-24 | 123456784  |
| 6          | 2021-11-24 | 2021-11-24 00:06:00 | 2021-06-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-25 | 123456785  |
| 7          | 2021-11-23 | 2021-11-23 00:07:00 | 2021-07-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-26 | 123456786  |
| 8          | 2021-11-22 | 2021-11-22 00:08:00 | 2021-08-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-27 | 123456787  |
| 9          | 2021-11-21 | 2021-11-21 00:09:00 | 2021-09-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-28 | 123456788  |
| 10         | 2021-11-20 | 2021-11-20 00:10:00 | 2021-10-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-29 | 123456789  |
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+

使用示例：静态数据示例

示例1：常见用法。

--返回2005-03-01 00:00:00。加1天，结果超出当年2月份的最后一天，实际值为下个月的第1天。
select dateadd(datetime '2005-02-28 00:00:00', 1, 'dd');
--返回2005-02-27 00:00:00。减1天。
select dateadd(datetime '2005-02-28 00:00:00', -1, 'dd');
--返回2006-10-28 00:00:00。加20个月，月份溢出，年份加1。
select dateadd(datetime '2005-02-28 00:00:00', 20, 'mm');
--返回2005-03-28 00:00:00。
select dateadd(datetime '2005-02-28 00:00:00', 1, 'mm');
--返回2005-02-28 00:00:00。2005年2月没有29日，日期截取至当月最后1天。
select dateadd(datetime '2005-01-29 00:00:00', 1, 'mm');
--返回2005-02-28 00:00:00。
select dateadd(datetime '2005-03-30 00:00:00', -1, 'mm');
--返回2005-03-18。
select dateadd(date '2005-02-18', 1, 'mm');
--开启2.0新类型。此命令需要与SQL语句一起提交。返回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');
--假设当前时间为2020-11-17 16:31:44，返回2020-11-16 16:31:44。
select dateadd(getdate(),-1,'dd');
--返回NULL。
select dateadd(date '2005-02-18', 1, null);

示例2：用常量表示。
在MaxCompute SQL中，DATETIME类型没有直接的常量表示方式，错误命令示例如下：
```
select dateadd(2005-03-30 00:00:00, -1, 'mm');
```
如果需要描述DATETIME类型的常量，正确命令示例如下：
```
--将STRING类型常量显式转换为DATETIME类型，返回2005-02-28 00:00:00。
select dateadd(cast("2005-03-30 00:00:00" as datetime), -1, 'mm');
```

示例3：输入参数为STRING类型。

--如下输入参数为STRING类型，但不符合DATETIME类型格式，会返回报错。
select dateadd('2021-08-27',1,'dd');
--如下输入参数为STRING类型，符合DATETIME类型格式，且MaxCompute项目的数据类型版本为1.0，返回2005-03-01 00:00:00。
set odps.sql.type.system.odps2=false;
select dateadd('2005-02-28 00:00:00', 1, 'dd');

使用示例：表数据示例

基于示例数据，对date1、datetime1和timestamp1列按照指定单位和幅度变化，命令示例如下。

--开启2.0新类型。此命令需要与SQL语句一起提交。
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;

返回结果如下。

+------------+---------------+---------------------+---------------------+-------------------------------+-------------------------------+
| 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 |
+------------+---------------+---------------------+---------------------+-------------------------------+-------------------------------+