How to Compare Two Dates in MySQL

1. Creating a Table and Inserting Values
2. Comparing Two Dates Using the WHERE Clause
3. Compare Two Dates Using the BETWEEN Operator
4. Compare Two Dates Using the DATE_ADD Function

This article explains comparing two dates in MySQL. There are three different approaches to accomplishing this task.

1. Using the WHERE clause
2. Using the BETWEEN operator
3. Using the DATE_ADD function

Creating a Table and Inserting Values

Firstly, I will create a table for employee as

Create table employee (empid int primary key, fname varchar(10), lname varchar(20), logindate timestamp not null default CURRENT_TIMESTAMP); 

Now, I will insert a few values in the table for demonstration.

INSERT INTO employee('empid', 'fname', 'lname')VALUES (1,'aaa','bbb'); 

INSERT INTO employee VALUES 
(201,'Peter','Parker','2001-01-01 16:15:00'), 
(202,'Thor','Odinson','2021-08-02 12:15:00'), 
(204,'Loki','Laufeyson','2009-08-03 10:43:24'); 

Comparing Two Dates Using the WHERE Clause

The query below is a very straightforward approach using the SELECT query that extracts all the records between the given range of dates.

SELECT * FROM employee WHERE logindate >= '2000-07-05' AND logindate < '2011-11-10'; 

Compare Two Dates Using the BETWEEN Operator

Firstly, We have to convert date to string in MySQL. To accomplish this task, we will use the DATE function to extract the date from a Datetime.

Syntax of DATE function is below.

DATE(column name)

If you wish to take advantage of an index on the column logindate you can try this instead. Between operator can also be used to select all the records that have the date column between two specified date expressions.

SELECT * FROM employee WHERE DATE(logindate) BETWEEN '2000-07-05' AND '2011-11-10'; 

MySQL only allows one yyyy-mm-dd date format, so whenever you need to format any string date expression you will have to use this format.

Now, you may have a query as to what is the need for the DATE() function in comparison?

So, MySQL DATE() function extracts the date part from your DATETIME or TIMESTAMP column into a string as shown below:

mysql> SELECT DATE('2005-08-28 01:02:03'); -> '2005-08-28' 

The DATE() function is used so that MySQL will consider only the date part of your column values for comparison. When you don’t use the DATE() function, then MySQL will compare the time portion of your column with your string expression. Using this option, any comparison will include the specified string expression as part of the result set

When comparing a DATETIME or TIME-STAMP column with a string representing a date like in the query above, MySQL will transform both column and expression values into long integer types for comparison.

This is the reason that even though you are comparing a date column with a string, you don’t manually need to convert your date column values into a string.

Compare Two Dates Using the DATE_ADD Function

Another approach is using the DATE_ADD() function. Although, this approach doesn’t necessarily need the DATE_ADD() function. But if you want to consider a few mins after the desired date to be a part of the solution you can use this. For example, If I want all the records between the given Date Range but can consider the records that were inserted 15 mins after the specified limit. This function is very useful.

DATE_ADD() function is used to add a specified time or date interval to a given date and then return the date. DATE_ADD function takes two parameters, first parameter is date and second is the interval to be added

select * from employee 
where logindate between '2000-07-05' and DATE_ADD('2011-07-10',INTERVAL 15 MINUTE);