. Advertisement .
..3..
. Advertisement .
..4..
Date and time selection and manipulation is a common task in programming, especially when you have to deal with a database. This brief introduction will show you how to compare dates in MySQL. Getting a firm grasp of this task will allow you to pick up more advanced techniques.
How To Compare Dates In MySQL
When you need to perform date comparisons in MySQL, the first function you should have in mind is DATE(), whose syntax is:
DATE(date_exp);The function has one required argument, which must be a date or datetime expression. DATE() will extract and return the date part of that expression. When given an invalid expression, it will return NULL.
Example:
mysql> SELECT DATE('2022-06-16 09:30:0');
-> ''2022-06-16'
You can integrate the DATE() function with SELECT() to carry out more complicated tasks.
Suppose you have a table named movie_rental like this, which contains information for a video rental shop.
|rental_id|rental_date |customer_id|return_date |
|---------|-----------------------|-----------|-----------------------|
|1,404 |2005-06-15 16:38:53.000|375 |2005-06-16 20:37:53.000|
|3,440 |2005-06-21 19:58:18.000|491 |2005-06-27 22:08:18.000|
|15,422 |2005-08-22 23:58:09.000|309 |2005-08-30 19:03:09.000|
|2,960 |2005-06-20 07:10:09.000|16 |2005-06-26 08:14:09.000|
|1,219 |2005-06-15 03:25:59.000|330 |2005-06-20 00:37:59.000|
|7,883 |2005-07-28 10:37:20.000|526 |2005-08-02 05:08:20.000|
|894 |2005-05-30 08:31:31.000|400 |2005-06-07 12:39:31.000|
|13,594 |2005-08-20 05:53:31.000|7 |2005-08-23 09:01:31.000|
|10,553 |2005-08-01 12:54:06.000|415 |2005-08-06 15:27:06.000|
|15,821 |2005-08-23 15:03:58.000|233 |2005-08-24 17:46:58.000|
This statement will list only rentals made before August 2005.
SELECT * FROM movie_rental WHERE DATE(rental_date) < "2005-08-01" ORDER BY rental_date ASC;Output:
|rental_id|rental_date |customer_id|return_date |
|---------|-----------------------|-----------|-----------------------|
|894 |2005-05-30 08:31:31.000|400 |2005-06-07 12:39:31.000|
|1,219 |2005-06-15 03:25:59.000|330 |2005-06-20 00:37:59.000|
|1,404 |2005-06-15 16:38:53.000|375 |2005-06-16 20:37:53.000|
|2,960 |2005-06-20 07:10:09.000|16 |2005-06-26 08:14:09.000|
|3,440 |2005-06-21 19:58:18.000|491 |2005-06-27 22:08:18.000|
|7,883 |2005-07-28 10:37:20.000|526 |2005-08-02 05:08:20.000|
You can use the BETWEEN operator to select rentals in May and June.
SELECT * FROM movie_rental WHERE DATE(rental_date) BETWEEN "2005-05-01" AND "2005-06-30" ORDER BY rental_date ASC;Output:
|rental_id|rental_date |customer_id|return_date |
|---------|-----------------------|-----------|-----------------------|
|894 |2005-05-30 08:31:31.000|400 |2005-06-07 12:39:31.000|
|1,219 |2005-06-15 03:25:59.000|330 |2005-06-20 00:37:59.000|
|1,404 |2005-06-15 16:38:53.000|375 |2005-06-16 20:37:53.000|
|2,960 |2005-06-20 07:10:09.000|16 |2005-06-26 08:14:09.000|
|3,440 |2005-06-21 19:58:18.000|491 |2005-06-27 22:08:18.000|
Meanwhile, this statement gives you rentals whose customers have returned the movies within a week.
SELECT * FROM movie_rental WHERE DATE(return_date) - DATE(rental_date) <=7 ORDER BY rental_date ASC;Output:
|rental_id|rental_date |customer_id|return_date |
|---------|-----------------------|-----------|-----------------------|
|1,219 |2005-06-15 03:25:59.000|330 |2005-06-20 00:37:59.000|
|1,404 |2005-06-15 16:38:53.000|375 |2005-06-16 20:37:53.000|
|2,960 |2005-06-20 07:10:09.000|16 |2005-06-26 08:14:09.000|
|3,440 |2005-06-21 19:58:18.000|491 |2005-06-27 22:08:18.000|
|10,553 |2005-08-01 12:54:06.000|415 |2005-08-06 15:27:06.000|
|13,594 |2005-08-20 05:53:31.000|7 |2005-08-23 09:01:31.000|
|15,821 |2005-08-23 15:03:58.000|233 |2005-08-24 17:46:58.000|
Conclusion
The DATE() functions allow you to compare dates in MySQL. It is among several built-in functions designed for date and time manipulation.
Leave a comment