Return the Number of Days in a Month in MariaDB

Here’s a nifty trick we can use in MariaDB to return the number of days in a month based on a given date.

SELECT DAYOFMONTH(LAST_DAY('2027-07-11'));

Result:

This involves passing MariaDB’s LAST_DAY() function to the DAYOFMONTH() function to return the number of days in the month.

More specifically, we passed the date to the LAST_DAY() function in order to get the date of the last day of the month. We then used the DAYOFMONTH() function to return just the day number of that last day.

Here’s an example that goes through all months of the year:

SELECT 
    DAYOFMONTH(LAST_DAY('2027-01-11')) AS "Jan",
    DAYOFMONTH(LAST_DAY('2027-02-11')) AS "Feb",
    DAYOFMONTH(LAST_DAY('2027-03-11')) AS "Mar",
    DAYOFMONTH(LAST_DAY('2027-04-11')) AS "Apr",
    DAYOFMONTH(LAST_DAY('2027-05-11')) AS "May",
    DAYOFMONTH(LAST_DAY('2027-06-11')) AS "Jun",
    DAYOFMONTH(LAST_DAY('2027-07-11')) AS "Jul",
    DAYOFMONTH(LAST_DAY('2027-08-11')) AS "Aug",
    DAYOFMONTH(LAST_DAY('2027-09-11')) AS "Sep",
    DAYOFMONTH(LAST_DAY('2027-10-11')) AS "Oct",
    DAYOFMONTH(LAST_DAY('2027-11-11')) AS "Nov",
    DAYOFMONTH(LAST_DAY('2027-12-11')) AS "Dec";

Result:

+------+------+------+------+------+------+------+------+------+------+------+------+
| Jan  | Feb  | Mar  | Apr  | May  | Jun  | Jul  | Aug  | Sep  | Oct  | Nov  | Dec  |
+------+------+------+------+------+------+------+------+------+------+------+------+
|   31 |   28 |   31 |   30 |   31 |   30 |   31 |   31 |   30 |   31 |   30 |   31 |
+------+------+------+------+------+------+------+------+------+------+------+------+