

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- 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)
- Related Questions & Answers
- How to compare DateTime Column with only Date not time in MySQL?
- How to part DATE and TIME from DATETIME in MySQL?
- How to split the datetime column into date and time and compare individually in MySQL?
- Compare DATE string with string from MySQL DATETIME field?
- Create DATETIME from DATE and TIME in MySQL?
- How to convert JS date time to MySQL datetime?
- Convert from varchar to datetime and compare in MySQL?
- How to compare the first date and the last date from a MySQL column with date records?
- How to compare Python DateTime with Javascript DateTime?
- Add DATE and TIME fields to get DATETIME field in MySQL?
- MySQL Query to convert from datetime to date?
- MySQL query to return the entire date and time based on a string and format
- How to select only MySQL date from datetime column?
- Combine date and time column into a timestamp in MySQL?
- How to calculate the difference between time in different MySQL columns?
Advertisements