MySQL includes dozens of useful date and time functions. For example, you can retrieve the last day of a month using MySQL’s LAST_DAY function. But there’s no equivalent FIRST_DAY function.

Yet, you can calculate the date of the first day of a month and this tutorial shows you how!

MySQL Series Overview

Get the First Day of a Month in MySQL

MySQL doesn’t have a built-in function to retrieve the first day of a month. You must calculate the first day using the MySQL date functions.

Here’s how: create a date format for the first day of a month and then cast that date format to a MySQL date. Here’s the sample code for that casting that uses NOW() as the date input. You can use any date or date-time for the calculation:

SELECT CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE);  
-- '2024-08-31'

You can use the calculation for the first day of a month in filters, too. For example, you may retrieve all users that signed up during the current month using the first day in combination with the LAST_DAY function:

SELECT *  
FROM users  
WHERE  
    createdon BETWEEN 
        CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE)
        AND LAST_DAY(NOW());

Notice: MySQL’s BETWEEN operator is inclusive. It includes all values greater or equal to the provided min value. It also includes values lower or equal to your max value.

Because we’re using the MySQL LAST_DAY function, you might be interested in related tutorials here on Future Studio:

• Get the Last Day of the Month in MySQL

That’s it!

Mentioned Resources

• MySQL docs for date and time functions
• Future Studio tutorial on how to get the last day of a month in MySQL