How to compare dates in MySQL

Overview of MySQL date data types

In MySQL, you can compare two dates as string expressions. Date data types are used to store date and/or time values in a database table. MySQL supports the following date data types:

Further, we'll explore the approaches to compare dates in MySQL. As a prerequisite, we have created the Persons table in the Sakila database and populated it with data using the Data Generator tool built into dbForge Studio for MySQL. The tool helped us generate random data with a few clicks.

The syntax of the DATE() function

As mentioned earlier, a date in the MySQL table column can have either DATE, DATETIME, TIMESTAMP, TIME, or YEAR value. If the input date value mismatch the data type of the date stored in the column, you can convert a string into a value of the DATE data type. This can be done with the help of the DATE() function, which extracts the date part from the specified date/datetime expression. The syntax of the DATE() function would be:

DATE ('YYYY-MM-DD');

For example, if we retrieve the hire dates from the Persons table by executing the SELECT statement with the specified criterion in the WHERE clause, we'll get the following result - the DATE() function returned the date part without the time part as specified in the condition:

Compare dates

Using the comparison operators is the simplest way to compare dates MySQL. They can help you define whether a given date value equals or is higher or less than the date values stored in the database table. Let's see the example of how to compare dates in MySQL using the comparison operators.

Scenario: Manager asked us to prepare a list of employees who were hired from January 1, 2020 to December 31, 2021. For this, we execute the SELECT statement with the comparison operators, such as greater than or equal (>=) or less than or equal (<=), in the WHERE clause.

SELECT
    p.PersonID,
    p.LastName,
    p.FirstName,
    p.city,
    p.HireDate
FROM persons p
WHERE p.HireDate >= '2020-01-01'
AND p.HireDate <= '2021-12-31'
ORDER BY p.HireDate ASC;

MySQL compared the specified date with the dates stored in the HireDate column, and in the output the statement returned the dates matching the range specified in the WHERE clause.

Compare between two dates

You can compare dates within a range. For this, you can use the BETWEEN operator in the SELECT statement:

SELECT columns FROM table_name WHERE column BETWEEN value1 AND value2;

where value1 and value2 are the values specifying the range within which you want to select dates.

The output returns the values that match the specified date range in the WHERE clause after date comparison.

Use the DATE_ADD function

For the date comparison, you can also use the DATE_ADD function. For example, this approach can be used for arithmetic operations. In MySQL, the DATE_ADD function returns the date to which the specified time or date interval was added. The syntax of the DATE_ADD function would be:

DATE_ADD(date, INTERVAL value expression_unit);

where:

• date is the date to be modified.
• value is the date or time interval to add.
• expression unit is a type of interval, such as year, month, day, hours, minutes, seconds, etc.

For example, consider the following:

SELECT DATE_ADD('2023-01-19', INTERVAL 3 MONTH);

In the output, the function returned a new date - April 19, 2023 - which is 3 month greater than the specified one.

Compare a Date to Now in MySQL

If you need to compare a date with the current date, you can use the NOW() function in the WHERE clause. The function fetches the current date.

Consider the following query:

SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  date(c.create_date)
FROM customer c
WHERE  DATE(c.create_date) = date(NOW());

The date values from the create_date column stored in the customer table will be compared to the current date and then the SELECT statement will extract only those dates that match the current date. Note that the current date used for the example is January 20, 2023.

Compare dates with timestamp

In this block, we are going to compare a timestamp date with the date part of the date value. For data comparison, we'll use the CAST() function to convert a datetime value to a date value. For this, we use the SELECT statement with the condition specified in the WHERE clause.

The CAST() function can be used to convert a value from one data type to another. It accepts the following parameters:

CAST(value AS datatype);

where value is the value to convert, and datatype is the data type to be converted to.

In the example, we extract dates stored in the create_date column from the customer table. As a condition, we specify that the date part of the datetime values should be equals to the value specified in the WHERE clause. For the conversion, we'll use the CAST() function.

As you can see, MySQL compared the input date value to the dates stored in the customer table and returned the dates according to the given criterion after the conversion.

Conclusion

In the article, we explored how to compare dates using the NOW(), DATE(), DATE_ADD() functions and comparison operators. All the examples were demonstrated in dbForge Studio for MySQL. Due to the variety of features and capabilities that the Studio has, such as the MySQL code validator tool or MySQL code formatter, you can work within a single IDE to perform different database development, management, testing, analysis, and deployment tasks. Enjoy your experience with dbForge Studio for MySQL and do not get surprised when your productivity will significantly increase and time for routine daily tasks will drastically reduce. Of course, that's not all the Studio does. Read more about the versatile features of the Studio!