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.

1. WHERE clause
2. BETWEEN operator
3. DATE ADD function

Building a Table and Adding Values

Start by making a table for the employees.

Now add a few values to the table to serve as an example.

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.

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.

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.

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.