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