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)

Samual Sam

Published on 19-Mar-2019 08:54:53

Previous Page Print Page