How to Get MySQL Today’s Date

Summary: in this tutorial, you will learn how to query data that matches 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 the 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)

Alternatively, 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 the date and time part, you should use the DATE() function to extract only the date part and compare it with the 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 frequently the CURDATE() function in your queries and want to replace them with the today() function for enhanced readability, you can create your own stored function named today() as follows:

DELIMITER $$

CREATE FUNCTION today()
RETURNS DATE
DETERMINISTIC
BEGIN
   RETURN CURDATE();
END$$

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

Now, you can use the today() function as follows:

SELECT today();

Output:

+------------+
| 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 the following query:

SELECT today() + interval 1 day Tomorrow;

Output:

+------------+
| 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:

SELECT today() - interval 1 day Yesterday;

Output:

+------------+
| Yesterday  |
+------------+
| 2017-07-07 |
+------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Getting employees whose birthday is today

Sometimes, you want to retrieve employees whose birthday is today. Let’s see an example.

First, create a table called employees:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    birthday DATE NOT NULL
);Code language: PHP (php)

Second, insert some rows into the employees table:

INSERT INTO employees (name, birthday) 
VALUES
    ('John Doe', '1990-01-06'),
    ('Jane Smith', '1985-08-22'),
    ('Bob Johnson', '1993-03-10'),
    ('Alice Brown', '1988-11-05'),
    ('Charlie White', '1995-07-18'),
    ('Eva Davis', '1982-09-30'),
    ('Mike Miller', '1998-01-25'),
    ('Sarah Jones', '1987-06-12'),
    ('David Taylor', '1991-12-08'),
    ('Emily Wilson', '1984-04-03');Code language: JavaScript (javascript)

Third, find the employees whose birthday is today:

SELECT *
FROM employees
WHERE DAY(birthday) = DAY(CURDATE()) 
      AND MONTH(birthday) = MONTH(CURDATE());

The query retrieves employees whose month and day of birthday are the same as the month and day of today.

Summary

• Use the CURDATE() or NOW() function to return the today’s date.