- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
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
MySQL to perform DateTime comparison and find the difference between dates in different columns
For this, use DATEDIFF() function. Let us first create a table −
mysql> create table DemoTable(DOB datetime,CurrentDate datetime); Query OK, 0 rows affected (0.59 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable values('1995-01-21',CURDATE()); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('1998-11-01',CURDATE()); Query OK, 1 row affected (0.39 sec) mysql> insert into DemoTable values('2000-10-24',CURDATE()); Query OK, 1 row affected (0.22 sec)
Display all records from the table using select statement −
mysql> select *from DemoTable;
This will produce the following output −
+---------------------+---------------------+ | DOB | CurrentDate | +---------------------+---------------------+ | 1995-01-21 00:00:00 | 2019-07-08 00:00:00 | | 1998-11-01 00:00:00 | 2019-07-08 00:00:00 | | 2000-10-24 00:00:00 | 2019-07-08 00:00:00 | +---------------------+---------------------+ 3 rows in set (0.00 sec)
Following is the query for datetime comparison −
mysql> select *from DemoTable where (datediff(CurrentDate,DOB) / 365) > 23;
This will produce the following output −
+---------------------+---------------------+ | DOB | CurrentDate | +---------------------+---------------------+ | 1995-01-21 00:00:00 | 2019-07-08 00:00:00 | +---------------------+---------------------+ 1 row in set (0.00 sec)
- Related Articles
- Perform MySQL search between two dates
- How to calculate the difference between time in different MySQL columns?
- MySQL date comparison to fetch dates between a given range?
- MySQL query to calculate the days between two dates from different columns but similar rows
- Find the difference between two datetime values with MySQL?
- Get the time difference between values in different columns with MySQL
- How to combine date and time from different MySQL columns to compare with the entire DateTime?
- Find the difference between dates in the form of months with MySQL
- Get the difference between dates and calculate salary with MySQL?
- Difference between datetime and datetime-local in HTML5
- What is the difference between MySQL DATETIME and TIMESTAMP data type?
- MySQL query to order and display difference between dates from the current date
- Comparison of dates in PHP
- Sorted difference between two columns in MySQL?
- MySQL select distinct dates from datetime column in a table?

Advertisements