LASTDAY-MaxCompute(MaxCompute)-阿里云帮助中心

Returns the last day of the month for a given DATETIME value. The time component of the return value is always 00:00:00.

Syntax

datetime LASTDAY(datetime <date>)

Parameters

Parameter	Type	Format	Description
`date`	DATETIME	`yyyy-mm-dd hh:mi:ss`	The input datetime value. If the project uses MaxCompute V1.0 data type edition and the input is STRING type, it is implicitly converted to DATETIME before the function runs.

Return value

Returns a DATETIME value in yyyy-mm-dd hh:mi:ss format. The day component is set to the last day of the input month; the time component is always 00:00:00.

Condition	Return value
Input is not DATETIME or STRING type, or the format is invalid	Error
Input is `null`	`null`

Examples

Static examples

-- Returns 2013-06-30 00:00:00
SELECT LASTDAY(DATETIME '2013-06-08 01:10:00');

-- Returns 2013-06-30 00:00:00 (STRING input with MaxCompute V1.0 data type edition)
SET odps.sql.type.system.odps2=false;
SELECT LASTDAY('2013-06-08 01:10:00');

-- Returns null
SELECT LASTDAY(null);

Table example

The following example uses the mf_date_fun_t sample table. For details on how to create and populate the table, see Sample data.

Get the last day of the month for each value in the datetime1 column:

SELECT datetime1, LASTDAY(datetime1) AS datetime1_lastday
FROM mf_date_fun_t;

Result:

+---------------------+---------------------+
| datetime1           | datetime1_lastday   |
+---------------------+---------------------+
| 2021-11-29 00:01:00 | 2021-11-30 00:00:00 |
| 2021-11-28 00:02:00 | 2021-11-30 00:00:00 |
| 2021-11-27 00:03:00 | 2021-11-30 00:00:00 |
| 2021-11-26 00:04:00 | 2021-11-30 00:00:00 |
| 2021-11-25 00:05:00 | 2021-11-30 00:00:00 |
| 2021-11-24 00:06:00 | 2021-11-30 00:00:00 |
| 2021-11-23 00:07:00 | 2021-11-30 00:00:00 |
| 2021-11-22 00:08:00 | 2021-11-30 00:00:00 |
| 2021-11-21 00:09:00 | 2021-11-30 00:00:00 |
| 2021-11-20 00:10:00 | 2021-11-30 00:00:00 |
+---------------------+---------------------+

Sample data

The examples in this topic use the mf_date_fun_t table. Run the following statements to create the table and insert data:

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 all rows to verify the data:

SELECT * FROM mf_date_fun_t;

Result:

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

Related functions

LASTDAY is a date function. For more information about date computing and conversion functions, see Date functions.