Returns the last day of the month in which a date value falls. This function is an additional function of MaxCompute V2.0.
Syntax
string last_day(date|datetime|timestamp|string <date>)Parameters
date: required. A date value of the DATE, DATETIME, TIMESTAMP, or STRING type. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and cannot contain extra strings.
Return value
A value of the STRING 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 date is not of the DATE, DATETIME, TIMESTAMP, or STRING type or the format does not meet the requirements, null or an invalid result is returned.
If the value of date is null, an error 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
-- The return value is 2017-03-31.
select last_day('2017-03-04');
-- The return value is 2017-07-31.
select last_day('2017-07-04 11:40:00');
-- The return value is null.
select last_day('20170304');
-- The return value is 2023-08-31.
select last_day('2023-08-01123');Example: table data
Obtain the last day of the month in which each date value in the date1, datetime1, timestamp1, and date3 columns falls. 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, last_day(date1) as date1_lastday, datetime1, last_day(datetime1) as datetime1_lastday, timestamp1, last_day(timestamp1) as timestamp1_lastday, date3, last_day(date3) as date3_lastday from mf_date_fun_t;The following result is returned:
+------------+---------------+---------------------+-------------------+-------------------------------+--------------------+------------+---------------+
| date1 | date1_lastday | datetime1 | datetime1_lastday | timestamp1 | timestamp1_lastday | date3 | date3_lastday |
+------------+---------------+---------------------+-------------------+-------------------------------+--------------------+------------+---------------+
| 2021-11-29 | 2021-11-30 | 2021-11-29 00:01:00 | 2021-11-30 | 2021-01-11 00:00:00.123456789 | 2021-01-31 | 2021-11-20 | 2021-11-30 |
| 2021-11-28 | 2021-11-30 | 2021-11-28 00:02:00 | 2021-11-30 | 2021-02-11 00:00:00.123456789 | 2021-02-28 | 2021-11-21 | 2021-11-30 |
| 2021-11-27 | 2021-11-30 | 2021-11-27 00:03:00 | 2021-11-30 | 2021-03-11 00:00:00.123456789 | 2021-03-31 | 2021-11-22 | 2021-11-30 |
| 2021-11-26 | 2021-11-30 | 2021-11-26 00:04:00 | 2021-11-30 | 2021-04-11 00:00:00.123456789 | 2021-04-30 | 2021-11-23 | 2021-11-30 |
| 2021-11-25 | 2021-11-30 | 2021-11-25 00:05:00 | 2021-11-30 | 2021-05-11 00:00:00.123456789 | 2021-05-31 | 2021-11-24 | 2021-11-30 |
| 2021-11-24 | 2021-11-30 | 2021-11-24 00:06:00 | 2021-11-30 | 2021-06-11 00:00:00.123456789 | 2021-06-30 | 2021-11-25 | 2021-11-30 |
| 2021-11-23 | 2021-11-30 | 2021-11-23 00:07:00 | 2021-11-30 | 2021-07-11 00:00:00.123456789 | 2021-07-31 | 2021-11-26 | 2021-11-30 |
| 2021-11-22 | 2021-11-30 | 2021-11-22 00:08:00 | 2021-11-30 | 2021-08-11 00:00:00.123456789 | 2021-08-31 | 2021-11-27 | 2021-11-30 |
| 2021-11-21 | 2021-11-30 | 2021-11-21 00:09:00 | 2021-11-30 | 2021-09-11 00:00:00.123456789 | 2021-09-30 | 2021-11-28 | 2021-11-30 |
| 2021-11-20 | 2021-11-30 | 2021-11-20 00:10:00 | 2021-11-30 | 2021-10-11 00:00:00.123456789 | 2021-10-31 | 2021-11-29 | 2021-11-30 |
+------------+---------------+---------------------+-------------------+-------------------------------+--------------------+------------+---------------+Related functions
LAST_DAY is a date function. For more information about functions related to date computing and conversion, see Date functions.