How to compare date in SQL

In this section, we will learn about how dates can be compared in SQL.

• We can compare any random date with another date stored in a column of a table.
• This comparison can be made with the help of comparison operators such >, <,   >=, >=, =.
• The date () function is also used in SQL to compare two different dates.
• The data type DATE allows storing the dates in SQL tables in ‘YYYY-MM-DD’ format. But while writing the query to compare the dates, the date to be written in the query can be in a relaxed string format.
• According to the relaxed string format, different parts of the date can be separated using any character in between. MySQL also allows a date to be written in a query without any separator, provided the string written as a date form a sensible date.

Example 1:

Write a query to find all the employees whose joining date is greater than or the same as 5^th May 1999.

Create a database with the name “dbemployee” with a table ‘employee’ created into it. We will consider this table and database for all the following examples.

mysql> CREATE DATABASE dbemployee;
Query OK, 1 row affected (0.00 sec)
mysql> USE dbemployee;
Database changed
mysql> CREATE TABLE employee (Emp_Id INT NOT NULL, Emp_Name VARCHAR (20), Emp_Dept VARCHAR (20), Emp_Salary INT, Emp_Joining_Date DATE);
Query OK, 0 rows affected (0.09 sec)

We have created a new database with the name ‘dbemployee’, and with ‘USE dbemployee’ command, we have selected this database. Then, with the ‘CREATE TABLE’ command, we have created a table ‘employee’ in the database ‘dbemployee’.

Now, we will insert data into the above created table.

mysql> INSERT INTO employee VALUES (1, "Sana Khan", "HRM", 45000, "1999-06-17");
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO employee VALUES (2, "Anupama Deshmukh", "Finance", 32000, CURDATE ());
Query OK, 1 row affected (0.11 sec)


mysql> INSERT INTO employee VALUES (3, "Kajal Shah", "Purchasing", 71000, "2020-12-12");
Query OK, 1 row affected (0.09 sec)


mysql> INSERT INTO employee VALUES (4, "Mayuri Koli", "Accounts", 64000, "1987-08-18");
Query OK, 1 row affected (0.09 sec)


mysql> INSERT INTO employee VALUES (5, "Surili Maheshwari", "Production", 30000, "1970-10-10");
Query OK, 1 row affected (0.09 sec)

After inserting data successfully into the table, we will now fetch all the records of a table.

mysql> SELECT *FROM employee;
+--------+-------------------+------------+------------+------------------+
| Emp_Id | Emp_Name          | Emp_Dept   | Emp_Salary | Emp_Joining_Date |
+--------+-------------------+------------+------------+------------------+
|      1 | Sana Khan         | HRM        |      45000 | 1999-06-17       |
|      2 | Anupama Deshmukh  | Finance    |      32000 | 2021-06-26       |
|      3 | Kajal Shah        | Purchasing |      71000 | 2020-12-12       |
|      4 | Mayuri Koli       | Accounts   |      64000 | 1987-08-18       |
|      5 | Surili Maheshwari | Production |      30000 | 1970-10-10       |
+--------+-------------------+------------+------------+------------------+
5 rows in set (0.00 sec)

Now, let’s write a query for the given problem statement.

mysql> SELECT *FROM employee WHERE Emp_Joining_Date >= '1999-05-05';

Output:

+--------+------------------+------------+------------+------------------+
| Emp_Id | Emp_Name         | Emp_Dept   | Emp_Salary | Emp_Joining_Date |
+--------+------------------+------------+------------+------------------+
|      1 | Sana Khan        | HRM        |      45000 | 1999-06-17       |
|      2 | Anupama Deshmukh | Finance    |      32000 | 2021-06-26       |
|      3 | Kajal Shah       | Purchasing |      71000 | 2020-12-12       |
+--------+------------------+------------+------------+------------------+
3 rows in set (0.00 sec)

There are three employees with employee ids 1, 2 and 3 whose joining date is greater than 5^th May 1999.

Example 2:

Write a query to find all the employees whose joining date is less than or the same as 5^th May 1999.

mysql> SELECT *FROM employee WHERE Emp_Joining_Date <= '19990505';

Output:

+--------+-------------------+------------+------------+------------------+
| Emp_Id | Emp_Name          | Emp_Dept   | Emp_Salary | Emp_Joining_Date |
+--------+-------------------+------------+------------+------------------+
|      4 | Mayuri Koli       | Accounts   |      64000 | 1987-08-18       |
|      5 | Surili Maheshwari | Production |      30000 | 1970-10-10       |
+--------+-------------------+------------+------------+------------------+
2 rows in set (0.00 sec)

Two employees with employee ids 4 and 5 whose joining date is less than 5^th May 1999.

Example 3:

Write a query to find all the employees whose joining date is same as 8^th August 1987.

mysql> SELECT *FROM employee WHERE Emp_Joining_Date = 19870818;

Output:

+--------+-------------+----------+------------+------------------+
| Emp_Id | Emp_Name    | Emp_Dept | Emp_Salary | Emp_Joining_Date |
+--------+-------------+----------+------------+------------------+
|      4 | Mayuri Koli | Accounts |      64000 | 1987-08-18       |
+--------+-------------+----------+------------+------------------+
1 row in set (0.00 sec)

There is only one employee with employee id 4 whose joining date is equal to 18^th August 1987.

Using date()

Example 4:

Write a query using the date () function to find all the employees whose joining date is the same as 26^th June 2021.

mysql> SELECT *FROM employee WHERE date (Emp_Joining_Date) = '2021-06-26';

Output:

+--------+------------------+----------+------------+------------------+
| Emp_Id | Emp_Name         | Emp_Dept | Emp_Salary | Emp_Joining_Date |
+--------+------------------+----------+------------+------------------+
|      2 | Anupama Deshmukh | Finance  |      32000 | 2021-06-26       |
+--------+------------------+----------+------------+------------------+
1 row in set (0.00 sec)

There is only one employee with employee id 2 whose joining date is equal to 26^th June 2021.