How to use last_day in mysql

MysqlHow-ToBeginner · 3 min read

How to Use LAST_DAY Function in MySQL: Syntax and Examples

In MySQL, use the LAST_DAY(date) function to get the last day of the month for a given date. It returns a date value representing the last day of the month that contains the input date.

📐

Syntax

The LAST_DAY() function takes one argument:

date: A date or datetime value from which you want to find the last day of its month.

The function returns a DATE value representing the last day of the month of the given date.

sql

LAST_DAY(date)

💻

Example

This example shows how to use LAST_DAY() to find the last day of the month for different dates.

sql

SELECT
  LAST_DAY('2024-06-15') AS last_day_june,
  LAST_DAY('2024-02-10') AS last_day_february,
  LAST_DAY(CURDATE()) AS last_day_current_month;

Output

last_day_june | last_day_february | last_day_current_month --------------|-------------------|----------------------- 2024-06-30 | 2024-02-29 | 2024-06-30

⚠️

Common Pitfalls

Common mistakes when using LAST_DAY() include:

Passing a NULL or invalid date, which returns NULL.
Expecting the function to return the last day of the current month without providing a date (you must pass a date explicitly or use CURDATE()).
Using LAST_DAY() on a datetime with a time part, which still works but returns only the date part.

sql

/* Wrong: Passing NULL returns NULL */
SELECT LAST_DAY(NULL) AS result;

/* Right: Use a valid date or CURDATE() */
SELECT LAST_DAY(CURDATE()) AS last_day;

Output

result ------ NULL last_day -------- 2024-06-30

📊

Quick Reference

Function	Description	Example	Returns
LAST_DAY(date)	Returns last day of month for given date	LAST_DAY('2024-06-15')	2024-06-30
LAST_DAY(CURDATE())	Last day of current month	LAST_DAY(CURDATE())	2024-06-30
LAST_DAY(NULL)	Returns NULL if input is NULL	LAST_DAY(NULL)	NULL

✅

Key Takeaways

Use LAST_DAY(date) to get the last day of the month for any given date.

Pass a valid date or CURDATE() to avoid NULL results.

LAST_DAY returns a DATE value, ignoring any time part.

It works correctly for leap years and different month lengths.

Common errors include passing NULL or invalid dates.