The date comparisons in MySQL

When we talk about the RDMS (relational database management system), the first name which comes to our mind is MySQL which is very much popular because of its features of high flexibility. MySQL manages the data of a website by using different functions including DATE(). In this writeup, we will discuss what the DATE() function is and how it can be used for comparison in MySQL.

What is the DATE() function in MySQL

The functions are used to obtain some specific results by running a single clause. The DATE() is also one of the functions which are used in MySQL to extract the date from the data of the table and data based on date, moreover, it can also be used to compare the dates of a table.

How the DATE() function is used for comparison in MySQL

The DATE() function can be used for the comparison of the date of a number of purposes, some of them are:

To extract the specific data on the basis of the date by using the inequality or equality signs
To extract the specific data on the basis of the date between different columns

We will explain both the above-mentioned scenarios with some examples, but before proceeding to it let us discuss the general syntax of the DATE() function.

The syntax of the DATE() function

The general syntax of using the DATE() is:

SELECT * FROM <table_name> WHERE DATE(<column_name>) <condition>;

The explanation of the above syntax is:

Use the “SELECT*FROM” clause to select the table and type the name of the table instead of table_name
Use the clause of the “WHERE” to apply the condition
Use the DATE function and replace the column_name whose date you want to compare and describe the condition

What should keep in mind

There is something you should remember while using the DATE() function:

The data of the table containing dates should be defined with the datatype DATE
The format of the dates inserted in the table should be [YYYY-MM-DD]
The date should be inserted in the form of string by using single apostrophes

How to use the DATE() function for comparison in MySQL

For more understanding let us consider an example of the table which is already created and can be displayed by using the command:

SELECT * FROM emp_data;

If we want to compare the joining date of all employees and extract the data of those employees who joined after 2000-01-01 using the DATE(), we can do so by running the command:

SELECT * FROM emp_data WHERE DATE(joining_date)>”2000-01-01”;

From the above output, it extracted those employees who joined the company after 2000-01-01, likewise to find out the oldest employees of the company we can execute the following command:

SELECT * FROM emp_data WHERE DATE(joining_date)<”2000-01-01”;

Similarly, if we want to extract the data of employees who get promoted between 2018-01-01 to 2021-01-31, we can find out by executing the command:

SELECT * FROM emp_data WHERE DATE(promotion_date) BETWEEN “2018-01-01” AND “2021-01-31”;

Conclusion

Functions make it easy for web developers to manage the data using them which take the one or two inputs and return a specific single output. DATE() is one of the functions which is used to obtain dates from the data of the table but can also be used for the comparison of data based on dates. In this write-up, we discussed the DATE() function used for the comparison purpose by explaining its general syntax and with the help of examples for better understanding.