返回一个日期的月份。此函数为MaxCompute 2.0扩展函数。
命令格式
int month(datetime|timestamp|date|string <date>)
参数说明
date:必填。DATETIME、TIMESTAMP、DATE或STRING类型日期值,格式为yyyy-mm-dd
、yyyy-mm-dd hh:mi:ss
或yyyy-mm-dd hh:mi:ss.ff3
。取值为STRING类型格式时,至少要包含yyyy-mm-dd
且不含多余的字符串。
返回值说明
返回INT类型。返回规则如下:
date非DATETIME、TIMESTAMP、DATE或STRING类型,或格式不符合要求时,返回NULL。
date值为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 |
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+
使用说明:静态数据示例
--返回9。
select month('2014-09-01');
--返回NULL。
select month('20140901');
--返回NULL。
select month(null);
使用说明:表数据示例
基于示例数据,返回date1、datetime1、timestamp1和date3列日期所属月份,命令示例如下。
--开启2.0新类型。此命令需要与SQL语句一起提交。
set odps.sql.type.system.odps2=true;
select date1, month(date1) as date1_month, datetime1, month(datetime1) as datetime1_month, timestamp1, month(timestamp1) as timestamp1_month, date3, month(date3) as date3_month from mf_date_fun_t;
返回结果如下。
+------------+-------------+---------------------+-----------------+-------------------------------+------------------+------------+-------------+
| date1 | date1_month | datetime1 | datetime1_month | timestamp1 | timestamp1_month | date3 | date3_month |
+------------+-------------+---------------------+-----------------+-------------------------------+------------------+------------+-------------+
| 2021-11-29 | 11 | 2021-11-29 00:01:00 | 11 | 2021-01-11 00:00:00.123456789 | 1 | 2021-11-20 | 11 |
| 2021-11-28 | 11 | 2021-11-28 00:02:00 | 11 | 2021-02-11 00:00:00.123456789 | 2 | 2021-11-21 | 11 |
| 2021-11-27 | 11 | 2021-11-27 00:03:00 | 11 | 2021-03-11 00:00:00.123456789 | 3 | 2021-11-22 | 11 |
| 2021-11-26 | 11 | 2021-11-26 00:04:00 | 11 | 2021-04-11 00:00:00.123456789 | 4 | 2021-11-23 | 11 |
| 2021-11-25 | 11 | 2021-11-25 00:05:00 | 11 | 2021-05-11 00:00:00.123456789 | 5 | 2021-11-24 | 11 |
| 2021-11-24 | 11 | 2021-11-24 00:06:00 | 11 | 2021-06-11 00:00:00.123456789 | 6 | 2021-11-25 | 11 |
| 2021-11-23 | 11 | 2021-11-23 00:07:00 | 11 | 2021-07-11 00:00:00.123456789 | 7 | 2021-11-26 | 11 |
| 2021-11-22 | 11 | 2021-11-22 00:08:00 | 11 | 2021-08-11 00:00:00.123456789 | 8 | 2021-11-27 | 11 |
| 2021-11-21 | 11 | 2021-11-21 00:09:00 | 11 | 2021-09-11 00:00:00.123456789 | 9 | 2021-11-28 | 11 |
| 2021-11-20 | 11 | 2021-11-20 00:10:00 | 11 | 2021-10-11 00:00:00.123456789 | 10 | 2021-11-29 | 11 |
+------------+-------------+---------------------+-----------------+-------------------------------+------------------+------------+-------------+
相关函数
MONTH函数属于日期函数,更多日期计算、日期转换的相关函数请参见日期函数。
文档内容是否对您有帮助?