You already have all your data in MySQL and for some decisions, you want to use statistics based on your data. For example, you may want to query the number of users that signed up during a given month. Or how many actions were triggered in a month.

This tutorial shows you how to filter rows with a date within a given month.

MySQL Series Overview

Using the MySQL DATE_FORMAT Function

MySQL comes with dozens of useful date and time functions. One of these functions is DATE_FORMAT allowing you to output a given date in a specified format. You can customize the format with unit specifiers. For example, a specifier for a year represented in four digits is %Y.

Let’s say you want to find all users who signed up in August 2024. Here’s a sample query you can use to filter these rows:

SELECT *  
FROM users  
WHERE  
    DATE_FORMAT(createdon, '%Y-%m') = '2024-08'

The first argument to DATE_FORMAT is the date input, and the second is the date specifier. You can then compare the output against your month of choice.

Notice: the DATE_FORMAT function returns NULL when either argument (input and specifier) is NULL.

Using the MySQL BETWEEN Function

Another option to filter dates within a month can be MySQL’s BETWEEN operator. BETWEEN checks whether a given input is greater than or equal to a given min value and less than or equal to a given max value. The BETWEEN comparison is inclusive and can be translated to this expression: min <= expr AND expr <= max.

Here’s a sample query filtering all rows where a user’s createdon timestamp is within August 2024.

SELECT *  
FROM users  
WHERE  
    createdon BETWEEN '2024-08-01' AND '2024-08-31'

You noticed that we hard-coded the min and max values for the BETWEEN comparison. You may also use MySQL’s LAST_DAY function. Here’s the rewritten SQL query filtering all users that signed up during August 2024:

SELECT *  
FROM users  
WHERE  
    createdon BETWEEN '2024-08-01' AND LAST_DAY('2024-08-01')

You might be interested in similar tutorials here on Future Studio that are related or helpful when working with dates in MySQL:

• Get the First Day of the Month in MySQL
• Get the Last Day of the Month in MySQL

Enjoy!

---

Mentioned Resources

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