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
How to combine date and time from different MySQL columns to compare with the entire DateTime?
You can combine date and time from different MySQL columns to compare with the entire date time with the help of CONCAT() function. The syntax is as follows −
SELECT *FROM yourTableName WHERE CONCAT(yourDateColumnName,'',yourTimeColumnName) > 'yourDateTimeValue';
To understand the above syntax, let us create a table. The query to create a table is as follows −
mysql> create table DifferentDateTime -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ArrivalDate date, -> ArrivalTime time -> ); Query OK, 0 rows affected (1.53 sec)
Insert some records in the table using insert command. The query is as follows −
mysql> insert into DifferentDateTime(ArrivalDate,ArrivalTime) values(curdate(),'10:27:30');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DifferentDateTime(ArrivalDate,ArrivalTime) values('2016-04-24','15:00:00');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DifferentDateTime(ArrivalDate,ArrivalTime) values('2015-12-25','12:30:30');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DifferentDateTime(ArrivalDate,ArrivalTime) values('2012-01-31','16:00:30');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DifferentDateTime(ArrivalDate,ArrivalTime) values('2000-07-12','09:45:31');
Query OK, 1 row affected (0.11 sec)Example
Display all records from the table using select statement. The query is as follows −
mysql> select *from DifferentDateTime;
Output
+----+-------------+-------------+ | Id | ArrivalDate | ArrivalTime | +----+-------------+-------------+ | 1 | 2019-02-14 | 10:27:30 | | 2 | 2016-04-24 | 15:00:00 | | 3 | 2015-12-25 | 12:30:30 | | 4 | 2012-01-31 | 16:00:30 | | 5 | 2000-07-12 | 09:45:31 | +----+-------------+-------------+ 5 rows in set (0.00 sec)
Example
Here is the query to combine date and time from different column and compare with date time value −
mysql> select *from DifferentDateTime where concat(ArrivalDate,'',ArrivalTime) > '2007-01-31 12:30:30';
Output
+----+-------------+-------------+ | Id | ArrivalDate | ArrivalTime | +----+-------------+-------------+ | 1 | 2019-02-14 | 10:27:30 | | 2 | 2016-04-24 | 15:00:00 | | 3 | 2015-12-25 | 12:30:30 | | 4 | 2012-01-31 | 16:00:30 | +----+-------------+-------------+ 4 rows in set (0.00 sec)
Updated on: 26-Jun-2020
880 Views
Advertisements