How to Get Yesterday’s Date in MySQL

Problem

You would like to display yesterday's date (without time) in a MySQL database.

Solution

SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS yesterday_date;

Assuming today is 2020-09-24, the result is:

Discussion

To get yesterday's date, you need to subtract one day from today's date. Use CURDATE() to get today's date. In MySQL, you can subtract any date interval using the DATE_SUB() function. Here, since you need to subtract one day, you use DATE_SUB(CURDATE(), INTERVAL 1 DAY) to get yesterday’s date. Note that the result of this calculation still has the column type date.

You can go back by any time interval just as easily. Here's an example:

SELECT DATE_SUB(CURDATE(), INTERVAL 2 MONTH) AS date_two_months_ago;

You can also calculate tomorrow's date very easily. Use the DATE_ADD() function to add an interval to a date.

SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY) AS tomorrow_date;