Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
Updated on: 30-Jul-2019
1K+ Views
Advertisements