How to Select Data from the Current Month in MySQL?

Selecting Data Within the Current Month in MySQL

In MySQL, you may need to extract data within a specific date range, often between the 1st day of the current month and the current day. To achieve this, we can use a combination of date functions.

Query:

SELECT * FROM table_name
WHERE date BETWEEN "1st day of current month" AND "current day";

Explanation:

The above query relies on hard-coded values for the start and end dates. However, these values vary based on the current system date. Instead, we can use dynamic date functions to determine these boundaries.

Improved Query (Option 1):

SELECT * FROM table_name
WHERE date BETWEEN DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 30 DAY), INTERVAL 1 DAY) AND CURDATE();

Breakdown:

• CURDATE() retrieves the current date.
• DATE_SUB(..., INTERVAL 30 DAY) subtracts 30 days from the current date to get the last day of the previous month.
• LAST_DAY(...) returns the last day of a given month (in this case, the previous month).
• DATE_ADD(...) adds one day to the result of the previous expression to get the 1st day of the current month.
• BETWEEN ... AND ... checks if the date column falls between the start and end dates.

Improved Query (Option 2):

SELECT * FROM table_name
WHERE date BETWEEN DATE_FORMAT(NOW(), '%Y-%m-01') AND NOW();

Breakdown:

• DATE_FORMAT(..., '%Y-%m-01') extracts the 1st day of the current month from the current date.
• NOW() retrieves the current date and time.
• BETWEEN ... AND ... checks if the date column falls between the start and end dates.

Usage:

These queries can be used to retrieve data within the specified date range, which is particularly useful for reporting or filtering purposes.

The above is the detailed content of How to Select Data from the Current Month in MySQL?. For more information, please follow other related articles on the PHP Chinese website!