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;