How to Select Rows Where Date Matches Day in MySQL

Web developers and database administrators regularly use date/time columns to filter rows pertaining to a given date or time range. Often you may need to select rows where date matches day in MySQL. In other words, you may need to select rows where the date value of a date/datetime column matches the present day. In this article, we will learn how to do this.

How to Select Rows Where Date Matches Day in MySQL

Let us say you have the following sales table in MySQL.

mysql> create table sales(id int, created_at datetime, amount int);

mysql> insert into sales(id, created_at, amount) 
       values(1, '2022-11-15 03:00:00', 180),
       (2, '2022-11-16 05:30:00', 100),
       (3, '2022-11-17 04:15:00', 280),
       (4, '2022-11-18 02:40:00', 150);

mysql> select * from sales;
+------+---------------------+--------+
| id   | created_at          | amount |
+------+---------------------+--------+
|    1 | 2022-11-15 03:00:00 |    180 |
|    2 | 2022-11-16 05:30:00 |    100 |
|    3 | 2022-11-17 04:15:00 |    280 |
|    4 | 2022-11-18 02:40:00 |    150 |
+------+---------------------+--------+

If you want to select rows where date values of created_at column matches today’s date, then here’s the query for it.

mysql> select * from sales where date(created_at)=date(now());
+------+------------+--------+
| id   | created_at | amount |
+------+------------+--------+
|    2 | 2022-11-16 |    100 |
+------+------------+--------+

You can also use current_date instead of now() in the above query.

mysql> select * from sales where date(created_at)=date(current_date);
+------+------------+--------+
| id   | created_at | amount |
+------+------------+--------+
|    2 | 2022-11-16 |    100 |
+------+------------+--------+

Please note, the above query is great and convenient if your table is small and less number of rows. If your table is large or has many rows, it is better to use literal strings to compare date value instead of using date() function because if your table is large then MySQL will need to calculate date() value for all values of column. Instead you can use the following query.

mysql> SELECT * FROM sales 
WHERE created_at >= '2022-11-16 00:00:00' AND created_at < '2022-11-16 23:59:59';

In the above query, MySQL can directly compare each value of created_at with the two literal strings and filter quickly. This is a much faster query than using date() function.

If speed is not a concern for you, and prefer simplicity then you can also use LIKE condition.

mysql> SELECT * FROM sales WHERE created_at LIKE '2022-11-16%';

In this article, we have learnt how to select rows where date matches day in MySQL.

Also read:

How to Copy Row to Same Table in MySQL
How to Run SQL Script in MySQL
How to Find Number of Columns in MySQL Table
How to Search for Text in Every Field of Database
How to Remove Leading & Trailing Whitespace in MySQL