Learn How To Compare Two Or More Datetime Entities In SQL With Examples
Introduction
In this tutorial, we'll go through SQL comparison of two dates.
There are three ways to go about completing this assignment.
- WHERE clause
- BETWEEN operator
- DATE ADD function
Building a Table and Adding Values
Start by making a table for the employees.
Create table employee (empid int primary key, fname varchar(10), lname varchar(20), logindate timestamp not null default CURRENT_TIMESTAMP); |
Now add a few values to the table to serve as an example.
INSERT INTO employee('empid', 'fname', 'lname')VALUES (1,'aaa','bbb'); |
Use WHERE clause
The extremely simple SELECT query used in the following query pulls all the records between the specified dates.
SELECT * FROM employee WHERE logindate >= '2000-07-05' AND logindate < '2011-11-10';
Use Between operator
First, we need to convert the date in MySQL to a string. We will extract the date from a Datetime using the DATE function to do this assignment. Below is the DATE function's syntax.
DATE(column name) |
Instead, you may attempt this if you want to benefit from an index on the column logindate. The between operator may also be used to choose any record with a date column that falls between two given date expressions.
SELECT * FROM employee WHERE DATE(logindate) BETWEEN '2000-07-05' AND '2011-11-10'; |
Every time you need to format a string date expression in MySQL, you must use the only supported yyyy-mm-dd date format. Now, you might wonder why the DATE() method is needed in comparison. So, as seen below, the MySQL DATE() function converts the date portion of your DATETIME or TIMESTAMP column into a string.
mysql> SELECT DATE('2005-08-28 01:02:03'); -> '2005-08-28' |
By using the DATE() method, MySQL will just compare the date component of your column values. If you don't utilize the DATE() method, MySQL will compare your string expression with the time component of your column. By selecting this option, the result set of any comparison will contain the supplied string expression.
For comparison, MySQL will convert the values of both the column and the expression into long integer types when comparing a DATETIME or TIME-STAMP column with a text that represents a date, as in the aforementioned query. This is the reason you don't need to manually convert the data in your date column into a string even if you are comparing a date column with a string.
Use DATE_ADD Function
Using the DATE ADD() method is an alternative strategy. Although the DATE ADD() function is not strictly necessary for this method. However, you may use this if you wish to think about the answer starting a few minutes after the ideal date. For instance, I might accept entries that were added 15 minutes after the provided limit if I require all data between the given Date Range. This feature is quite helpful. The DATE ADD() method adds a certain time or date interval to a provided date before returning the original date. Date and the interval to be added are the two arguments that the DATE ADD method accepts.
select * from employee |