In MariaDB, LAST_DAY()
is a built-in date and time function that returns the last day of the month for a given date.
It accepts one argument, which is the date for which you want to find the last day of the month.
Syntax
The syntax goes like this:
LAST_DAY(date)
Where date
is the date expression for which you want to find the last day of the month.
Example
Here’s an example:
SELECT LAST_DAY('2030-02-01');
Result:
+------------------------+ | LAST_DAY('2030-02-01') | +------------------------+ | 2030-02-28 | +------------------------+
In this case, we’re using a date in February. As it turns out, February has 28 days in that year.
Here’s what happens if we increment the date to the next leap year:
SELECT LAST_DAY('2032-02-01');
Result:
+------------------------+ | LAST_DAY('2032-02-01') | +------------------------+ | 2032-02-29 | +------------------------+
Datetime Values
It also works with datetime values:
SELECT LAST_DAY('2030-02-01 10:30:45');
Result:
+---------------------------------+ | LAST_DAY('2030-02-01 10:30:45') | +---------------------------------+ | 2030-02-28 | +---------------------------------+
Zero Dates
Zero dates result in null
.
Example:
SELECT LAST_DAY('0000-00-00');
Result:
+------------------------+ | LAST_DAY('0000-00-00') | +------------------------+ | NULL | +------------------------+
Numeric Dates
It’s also possible to pass dates as a number, as long as it makes sense as a date.
Example
SELECT LAST_DAY(20301125);
Result:
+--------------------+ | LAST_DAY(20301125) | +--------------------+ | 2030-11-30 | +--------------------+
Or even the following (which uses a two-digit year):
SELECT LAST_DAY(301125);
Result:
+------------------+ | LAST_DAY(301125) | +------------------+ | 2030-11-30 | +------------------+
But it must make sense as a date. Here’s what happens if I increase the day part to an invalid day:
SELECT LAST_DAY(20301135);
Result:
+--------------------+ | LAST_DAY(20301135) | +--------------------+ | NULL | +--------------------+ 1 row in set, 1 warning (0.000 sec)
We can check the warning like this:
SHOW WARNINGS;
Result:
+---------+------+--------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------+ | Warning | 1292 | Incorrect datetime value: '20301135' | +---------+------+--------------------------------------+
Other Delimiters
You can use other delimiters for the date. MariaDB is quite forgiving when it comes to delimiters on dates. Here are some valid examples:
SELECT
LAST_DAY('2030/06/25'),
LAST_DAY('2030,06,25'),
LAST_DAY('2030:06:25'),
LAST_DAY('2030;06!25');
Result (using vertical output):
LAST_DAY('2030/06/25'): 2030-06-30 LAST_DAY('2030,06,25'): 2030-06-30 LAST_DAY('2030:06:25'): 2030-06-30 LAST_DAY('2030;06!25'): 2030-06-30
Current Date
We can pass NOW()
as the datetime argument to use the current date:
SELECT
NOW(),
LAST_DAY(NOW());
Result:
+---------------------+-----------------+ | NOW() | LAST_DAY(NOW()) | +---------------------+-----------------+ | 2021-05-18 09:39:01 | 2021-05-31 | +---------------------+-----------------+
Invalid Arguments
When passed an invalid argument, LAST_DAY()
returns null
:
SELECT LAST_DAY('2030-65-78');
Result:
+------------------------+ | LAST_DAY('2030-65-78') | +------------------------+ | NULL | +------------------------+ 1 row in set, 1 warning (0.000 sec)
Check the warning:
SHOW WARNINGS;
Result:
+---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Warning | 1292 | Incorrect datetime value: '2030-65-78' | +---------+------+----------------------------------------+
Missing Argument
Calling LAST_DAY()
with the wrong number of arguments, or without passing any arguments, results in an error:
SELECT LAST_DAY();
Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'LAST_DAY'
And another example:
SELECT LAST_DAY('2030-12-10', '2031-12-10');
Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'LAST_DAY'