MariaDB/MySQL: How to get the first and last day of the month

Different databases support different ways to get the first and last day of the month. Let’s see how to do it in MariaDB and MySQL.

See this query:

SELECT
    DATE(CONCAT_WS('-', YEAR(CURRENT_DATE()), MONTH(CURRENT_DATE()), '01')) AS first,
    LAST_DAY(CURRENT_DATE()) AS last
;
+------------+------------+
| first      | last       |
+------------+------------+
| 2022-01-01 | 2022-01-31 |
+------------+------------+
1 row in set (0.001 sec)

Let’s see how/why it works.

YEAR() and MONTH() return the year and the month from a given date – in this case, the current date. With CONCAT_WS() (concatenate with separator), we compose a date with the current year and month, day 01. DATE() converts the string into a date, so we will have it formatted properly even if MONTH() returned a single digit.

Getting the last day is simpler: LAST_DAY() returns the last date of the month from a given date, in this case the current date.

Reference

Functions documentation is available here: