How to get the end day of a month in MariaDB

If you want to get the end day of the month for a given date in MariaDB, you can use the LAST_DAY() function.

LAST_DAY() accepts a date value or datetime value as an argument. If you pass in an incorrect value, LAST_DAY() will return NULL.

Simple example

To get the end day of December 2022, you can use the following statement:

SELECT LAST_DAY('2022-12-01');

Output:

+------------------------+
| LAST_DAY('2022-12-01') |
+------------------------+
| 2022-12-31             |
+------------------------+

You can also pass in a datetime value, as follows:

SELECT LAST_DAY('2022-12-01 01:01:01');

Output:

+---------------------------------+
| LAST_DAY('2022-12-01 01:01:01') |
+---------------------------------+
| 2022-12-31                      |
+---------------------------------+

In this example, the end day of December is 2022-12-31.

Get the end day of each month

The following statement gets the last day of each month in 2022:

SELECT
  LAST_DAY('2022-01-02') AS "01",
  LAST_DAY('2022-02-02') AS "02",
  LAST_DAY('2022-03-02') AS "03",
  LAST_DAY('2022-04-02') AS "04",
  LAST_DAY('2022-05-02') AS "05",
  LAST_DAY('2022-06-02') AS "06",
  LAST_DAY('2022-07-02') AS "07",
  LAST_DAY('2022-08-02') AS "08",
  LAST_DAY('2022-09-02') AS "09",
  LAST_DAY('2022-10-02') AS "10",
  LAST_DAY('2022-11-02') AS "11",
  LAST_DAY('2022-12-02') AS "12"\G

Output:

01: 2022-01-31
02: 2022-02-28
03: 2022-03-31
04: 2022-04-30
05: 2022-05-31
06: 2022-06-30
07: 2022-07-31
08: 2022-08-31
09: 2022-09-30
10: 2022-10-31
11: 2022-11-30
12: 2022-12-31

Database example

This example uses the staff table from the Sakila sample database :

The following statement gets the end of the month of the last_update column:

SELECT
  last_update,
  LAST_DAY(last_update)
FROM staff;

Output:

+---------------------+-----------------------+
| last_update         | LAST_DAY(last_update) |
+---------------------+-----------------------+
| 2006-02-15 03:57:16 | 2006-02-28            |
| 2006-02-15 03:57:16 | 2006-02-28            |
+---------------------+-----------------------+

Conclusion

In MariaDB, you can use the LAST_DAY() function to get the end day of the month.