EN
MySQL - compare datetime values
0
points
In this article, we would like to show you how to compare datetime values in MySQL.
Quick solution:
SELECT *
FROM `table_name`
WHERE `datetime_column_name` <= 'yyyy-mm-dd';
Notes:
- you can replace theĀ
<=
Ā operator with any other comparison operator,- to see how to use
BETWEEN
keyword with dates check this article.
Ā
Practical example
To show how to compare datetime values, we will use the following table:
Note:
At the end of this article you can find database preparation SQL queries.
Example 1
In this example, we will display all the datetimesĀ beforeĀ 2000-01-04
.
Query:
SELECT *
FROM `dates`
WHERE `date` < '2000-01-04';
Output:
Example 2
In this example, we will select all the datetimesĀ from 2000-01-04
inclusive and display them without timeĀ using DATE()
function (yyyy-mm-dd
Ā format only).
Query:
SELECT DATE(`date`)
FROM `dates`
WHERE `date` <= '2000-01-04';
Output:
Database preparation
create_tables.sql
Ā file:
create TABLE `dates`(
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`date` DATETIME,
PRIMARY KEY (`id`)
);
insert_data.sql
Ā file:
insert into `dates`
(`date`)
VALUES
('2000-01-01'),
('2000-01-02'),
('2000-01-03'),
('2000-01-04'),
('2000-01-05'),
('2000-01-06');