3 Ways to Get the First Day of the Month in MySQL

To get the first day of a month in MySQL, subtract the day number minus one, format the year and month with DATE_FORMAT(), or advance one day from the previous month’s last day. The examples use March 31, 2024, and all return March 1, 2024. See MySQL’s date and time function reference.

SELECT
    DATE_SUB('2024-03-31',
        INTERVAL (DAYOFMONTH('2024-03-31') - 1) DAY) AS with_date_sub,
    CAST(DATE_FORMAT('2024-03-31', '%Y-%m-01') AS DATE) AS with_date_format,
    DATE_ADD(
        LAST_DAY(DATE_SUB('2024-03-31', INTERVAL 1 MONTH)),
        INTERVAL 1 DAY
    ) AS with_last_day;

with_date_sub  with_date_format  with_last_day
-------------  ----------------  -------------
2024-03-01     2024-03-01        2024-03-01

1. Subtract the days before the first

DAYOFMONTH() returns the day number within the month. Subtract one less than that many days from the date:

SELECT DATE_SUB(
    '2024-03-31',
    INTERVAL (DAYOFMONTH('2024-03-31') - 1) DAY
) AS month_start;

This method returns a date value and works across month lengths, including February and leap years. Replace the date literal with a column or CURDATE() to use a table value or the current date. See SQLiz’s DATE_SUB() and DAYOFMONTH() references, and MySQL’s date function manual.

2. Build the first-day string with DATE_FORMAT()

The %Y-%m format produces the year and month. Append -01 in the format string to make the first day:

SELECT DATE_FORMAT('2024-03-31', '%Y-%m-01') AS month_start_text;

DATE_FORMAT() returns a string. Cast it to DATE if a date value is needed for comparison or further date arithmetic:

SELECT CAST(DATE_FORMAT('2024-03-31', '%Y-%m-01') AS DATE) AS month_start;

See SQLiz’s DATE_FORMAT() reference and MySQL’s date function manual.

3. Add one day to the previous month’s last day

Subtract one month from the input, find that month’s last day with LAST_DAY(), then add one day:

SELECT DATE_ADD(
    LAST_DAY(DATE_SUB('2024-03-31', INTERVAL 1 MONTH)),
    INTERVAL 1 DAY
) AS month_start;

LAST_DAY() handles months with different lengths, so this also works when the current month follows February. See SQLiz’s LAST_DAY() reference and MySQL’s date function manual.

Use the result with a DATETIME column

For a DATETIME column, compare a half-open range to select rows from the current month. The upper bound is the first day of the next month, so the query includes every time on the last day without applying a function to the indexed column:

SELECT order_id, created_at
FROM orders
WHERE created_at >= DATE_SUB(CURDATE(),
          INTERVAL (DAYOFMONTH(CURDATE()) - 1) DAY)
  AND created_at < DATE_ADD(
          DATE_SUB(CURDATE(),
              INTERVAL (DAYOFMONTH(CURDATE()) - 1) DAY),
          INTERVAL 1 MONTH
      );

If the input expression is DATETIME and you need a date at midnight, pass DATE(date_value) to the first method to discard its time portion. CURDATE() returns the current date; NOW() includes the current time.