How to Get MySQL Today’s Date
Summary: in this tutorial, you will learn how to query data that matches with the MySQL today‘s date by using built-in date functions.
Getting MySQL today’s date using built-in date functions
Sometimes, you may want to query data from a table to get rows with date column is today, for example:
SELECT
column_list
FROM
table_name
WHERE
expired_date = today;Code language: SQL (Structured Query Language) (sql)
To get today’s date, you use the CURDATE() function as follows:
mysql> SELECT CURDATE() today;
+------------+
| today |
+------------+
| 2017-07-08 |
+------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)
Or you can get the date part from the current time returned by the NOW() function:
mysql> SELECT DATE(NOW()) today;
+------------+
| today |
+------------+
| 2017-07-08 |
+------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)
So the query should change to:
SELECT
column_list
FROM
table_name
WHERE
expired_date = CURDATE();Code language: SQL (Structured Query Language) (sql)
If the expired_date column contains both date and time part, you should use the DATE() function to extract only the date part and compare it with current date:
SELECT
column_list
FROM
table_name
WHERE
DATE(expired_date) = CURDATE();Code language: SQL (Structured Query Language) (sql)
Creating your MySQL today stored function
If you use the CURDATE() function a lot in your queries and you want to replace it by the today() function to make the queries more readable, you can create your own stored function named today() as follows:
DELIMITER $$
CREATE FUNCTION today()
RETURNS DATE
BEGIN
RETURN CURDATE();
END$$
DELIMITER ;Code language: SQL (Structured Query Language) (sql)
Now, you can use the today() function that you have created as follows:
mysql> SELECT today();
+------------+
| today() |
+------------+
| 2017-07-08 |
+------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)
How about tomorrow? It should be as simple as:
mysql> SELECT today() + interval 1 day Tomorrow;
+------------+
| Tomorrow |
+------------+
| 2017-07-09 |
+------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)
And also yesterday is easy as well:
mysql> SELECT today() - interval 1 day Yesterday;
+------------+
| Yesterday |
+------------+
| 2017-07-07 |
+------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to get MySQL today’s date using built-in date function. You also learned how to develop your own today function using stored function in MySQL.