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
- Truncate a Foreign Key Constrained Table
- Add Days to a Datetime
- Find Value in Column with Comma-Separated Values
- Ideas to Debug High-Load Situations (Coming soon)
- Calculate Number of Days Between Two Dates
- Get the Last Day of the Month
- Get the First Day of the Month
- Select Rows With Date Within a Month
- Get Weekday From Date (Coming soon)
- Fixing Invalid JSON Text in Argument 1 to Function json_extract: "Invalid value." (Coming soon)
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:
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