How to Compare Date in MySQL
Dates can be confusing in MySQL if you don’t know how they are handled. Here we are going to show you how to compare dates in MySQL.
The simplest example would be where we have a column of type DATE and we want to check if it matches a specific date. Here is the example:
SELECT * FROM inventory WHERE recTime = '2015-04-25'
You can check if it is between two dates like this:
SELECT * FROM inventory WHERE recTime BETWEEN '2007-07-15' AND '2010-11-15'
Things are a bit different if we store values as a DATETIME but just want to compare the dates. When this is the case we generally convert DATETIME type to DATE type using the DATE() function.
Here is a really simple example in which we check if a column matches an exact date. We basically convert a datetime to a date and then compare it to a string representing a specific date.
SELECT * FROM inventory WHERE DATE(recTime) = '2015-04-25'
Here is an example where we convert a datetime to a date and then check if it falls between two other dates ( within a range ):
SELECT * FROM inventory WHERE DATE(my_dt) BETWEEN '2005-03-05' AND '2008-09-27'
Here is another example:
SELECT * FROM inventory WHERE my_dt BETWEEN '2000-07-05' AND DATE_ADD('2008-09-27',INTERVAL 1 DAY)
There are a few different types that can be used to store time and date information. Here is a chart:
|DATETIME||Stores both date and time||YYYY-MM-DD HH:MM:SS||‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.|
|DATE||Only stores the date, not time||YYYY-MM-DD||‘1000-01-01’ to ‘9999-12-31’.|
|TIMESTAMP||Values stored as UTC and converted to current TZ when retrieved||YYYY-MM-DD HH:MM:SS||‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC|
|TIME||Just stores time||HH:MM:SS||‘00:00:00’ to ‘23:59:59’|
|YEAR(M)||Just stores a year||YY or YYYY||YEAR(2): 1970 to 2069 (70 to 69) or YEAR(4): 1901 to 2155|