Compare DATE string with string from MySQL DATETIME field?

You can compare DATE string with string from DATETIME field with the help of DATE() function in MySQL.The syntax is as follows −

select *from yourTableName where DATE(yourColumnName) = ’anyDateString’;

To understand the above syntax, let us create a table and set some datetime values in the table. The query to create a table −

mysql> create table DateTimeDemo
   −> (
   −> ArrivalTime datetime
   −> );
Query OK, 0 rows affected (0.61 sec)

Let us insert some records in the table with the help of insert command. The following is the query to insert records −

mysql> insert into DateTimeDemo values(now());
Query OK, 1 row affected (0.11 sec)

mysql> insert into DateTimeDemo values(date_add(now(),interval 2 year));
Query OK, 1 row affected (0.47 sec)

mysql> insert into DateTimeDemo values(date_add(now(),interval 1 year));
Query OK, 1 row affected (0.19 sec)

mysql> insert into DateTimeDemo values(date_add(now(),interval 4 year));
Query OK, 1 row affected (0.13 sec)

mysql> insert into DateTimeDemo values(date_add(now(),interval -2 year));
Query OK, 1 row affected (0.17 sec)

mysql> insert into DateTimeDemo values(date_add(now(),interval -1 year));
Query OK, 1 row affected (0.17 sec)

Display all records with the help of select statement. The query to display all records from the table −

mysql> select *from DateTimeDemo;

The following is the output −

+---------------------+
| ArrivalTime         |
+---------------------+
| 2018-12-06 10:12:45 |
| 2020-12-06 10:13:10 |
| 2019-12-06 10:13:21 |
| 2022-12-06 10:13:27 |
| 2016-12-06 10:13:42 |
| 2017-12-06 10:13:50 |
+---------------------+
6 rows in set (0.00 sec)

Now implement the syntax we discussed above for comparing string with date time field. The query is as follows −

mysql> select *from DateTimeDemo where date(ArrivalTime) = '2022-12-06';

The following is the output −

+---------------------+
| ArrivalTime         |
+---------------------+
| 2022-12-06 10:13:27 |
+---------------------+
1 row in set (0.14 sec)