MySQL Date Comparison with DATE_FORMAT()

In this article, we will explore how to perform date comparisons in MySQL using the DATE_FORMAT() function, addressing a specific issue faced by a user while comparing dates.

The Issue

The user faced a challenge when comparing dates using DATE_FORMAT(). The dates were stored in the following format: '%d-%m-%Y', which is not an easily sortable format. Using the query below, the user attempted to compare the dates:

SELECT DATE_FORMAT(DATE(starttime), '%d-%m-%Y')
FROM data
WHERE DATE_FORMAT(DATE(starttime), '%d-%m-%Y') >= '02-11-2012';

However, the result included '28-10-2012', which was incorrect as per the user's expectations.

The Solution

The issue arises because we are comparing strings instead of dates. DATE_FORMAT() converts a date to a string, and strings are compared lexicographically. In this case, '28-10-2012' is greater than '02-11-2012' lexicographically, even though '02-11-2012' is a later date.

To accurately compare dates, we need to compare them as dates, not strings. We can use the DATE() function to extract the date component from the starttime field, and then compare the dates using the >= operator, as shown in the following query:

SELECT DATE_FORMAT(DATE(starttime), '%d-%m-%Y')
FROM data
WHERE DATE(starttime) >= DATE('2012-11-02');

This query will correctly compare the dates and exclude '28-10-2012' from the result.

Additional Consideration

It's worth considering whether the DATETIME field starttime can be changed to a DATE field. This would eliminate the need for repeated conversion, potentially improving performance.