Obtaining current-month records in MySQL is useful for reporting and data analysis. It allows you to get sales statistics for the current month, the number of signups for the current month, and other valuable data. Here's how to use MySQL to retrieve records for the current month.
How to Get Records of Current Month in MySQL
The procedures to retrieve current month records in MySQL are shown below. Additionally, we'll use it to attract new clients this month.
Assume you have the table sales(order date, sale, orders) below, which provides the daily number of orders and sale amount.
mysql> select * from sales; +------------+------+--------+ | order_date | sale | orders | +------------+------+--------+ | 2020-04-28 | 300 | 10 | | 2020-04-29 | 250 | 15 | | 2020-04-30 | 250 | 12 | | 2020-05-01 | 250 | 14 | | 2020-05-02 | 150 | 20 | | 2020-05-03 | 300 | 21 | | 2020-05-04 | 200 | 15 | | 2020-05-05 | 200 | 17 | | 2020-05-06 | 250 | 12 | | 2020-05-07 | 150 | 15 | | 2020-05-08 | 300 | 12 | | 2020-05-09 | 200 | 18 | +------------+------+--------+
How to Get Sales Data of Current Month in MySQL
Here’s the SQL query to get records of current month in MySQL
mysql> select * from sales where MONTH(order_date)=MONTH(now()) and YEAR(order_date)=YEAR(now()); +------------+------+--------+ | order_date | sale | orders | +------------+------+--------+ | 2020-05-01 | 250 | 14 | | 2020-05-02 | 150 | 20 | | 2020-05-03 | 300 | 21 | | 2020-05-04 | 200 | 15 | | 2020-05-05 | 200 | 17 | | 2020-05-06 | 250 | 12 | | 2020-05-07 | 150 | 15 | | 2020-05-08 | 300 | 12 | | 2020-05-09 | 200 | 18 | +------------+------+--------+
We utilize the system function now() to acquire the most recent date and time in the aforementioned query. Then, in MySQL, we obtain results for the current month by filtering out entries with the same month and year as the current time. The built-in MySQL methods MONTH() and YEAR() may be used to extract the month and year from a given date.
