MONTH
Updated at:
Returns the month component of a date value. This function is an additional function of MaxCompute V2.0.
Syntax
int month(datetime|timestamp|date|string <date>)Parameters
date: required. A date value of the DATETIME, TIMESTAMP, DATE, or STRING type. The input value must be in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss:ff3 format. If the value is of the STRING type, it must include at least the yyyy-mm-dd part and must not contain extra characters.
Return value
Returns an INT value in the range 1–12, where 1 represents January and 12 represents December.
Returns null in the following cases:
The
datevalue is not of the DATETIME, TIMESTAMP, DATE, or STRING type, or the format does not meet the requirements.The
datevalue is null.
Sample data
This topic provides sample data and examples to help you understand this function. You can create the `mf_date_fun_t` table and insert data into it by running the following command.
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 the data in the `mf_date_fun_t` table:
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 |
+------+-------+------------+------------+-------+------------+------------+-------+------------+Examples
Static values
-- Returns 9.
select month('2014-09-01');
-- Returns null. The format yyyy-mm-dd is required; yyyymmdd is not accepted.
select month('20140901');
-- Returns null.
select month(null);Table data
The following example extracts the month from the date1, datetime1, timestamp1, and date3 columns of the sample table. Enable the MaxCompute V2.0 data type edition before running the query:
-- Enable the MaxCompute V2.0 data type edition. Commit this command along with SQL statements.
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;The following result is returned:
+------------+-------------+---------------------+-----------------+-------------------------------+------------------+------------+-------------+
| 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 |
+------------+-------------+---------------------+-----------------+-------------------------------+------------------+------------+-------------+Related functions
MONTH is a date function. For more information about functions related to date computing and conversion, see Date functions.
Is this page helpful?