First and Last Day of a Month in MySQL

Month boundaries

Monthly reports need the first and last day of a month. Months have different lengths (28 to 31 days) and leap years complicate February, so calculating these by hand is error-prone. MySQL's LAST_DAY and DATE_FORMAT make it reliable.

LAST_DAY for the last day

SELECT LAST_DAY('2026-02-10');

LAST_DAY(date) returns the last day of that date's month. It correctly handles 28, 29, 30 and 31-day months, including leap years — no manual logic needed.

Getting the first day

SELECT DATE_FORMAT('2026-02-10', '%Y-%m-01');

There is no FIRST_DAY function, but formatting the date with day 01 gives the first day of the month. This works for any date you pass in.

This month automatically

SELECT
    DATE_FORMAT(CURDATE(), '%Y-%m-01') AS first_day,
    LAST_DAY(CURDATE()) AS last_day;

Using CURDATE() as the input gives the current month's boundaries, updating automatically as months change.

A monthly date range

SELECT * FROM orders
WHERE order_date BETWEEN
    DATE_FORMAT(CURDATE(), '%Y-%m-01')
    AND LAST_DAY(CURDATE());

Combine both to filter rows within the current month. This is the standard pattern for "this month's" reports — sales, sign-ups, or any dated records.

Previous month

SELECT
    DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m-01') AS prev_first,
    LAST_DAY(CURDATE() - INTERVAL 1 MONTH) AS prev_last;

Subtracting INTERVAL 1 MONTH before applying the functions gives last month's boundaries — useful for monthly comparisons and closing reports.

Common mistakes

• Hard-coding 30 or 31. Month lengths vary; let LAST_DAY handle it.
• Forgetting leap years. LAST_DAY handles February correctly.
• Looking for a FIRST_DAY function. There is none; use DATE_FORMAT with 01.
• Using > last day without <= . Use BETWEEN or inclusive bounds for the full month.

Summary

• LAST_DAY(date) gives the last day of the month, leap-year safe.
• DATE_FORMAT(date, '%Y-%m-01') gives the first day.
• Use CURDATE() for the current month, updating automatically.
• Combine both with BETWEEN for a monthly range.
• Subtract INTERVAL 1 MONTH for the previous month.

Next, compute ages in age from date of birth, or filter recent rows in records from the last 7 days.