Low Orbit Flux Logo 2 D

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:

Types Description Display Format Range
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