How to Get Yesterday’s Date in Oracle

Problem

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

Solution 1

SELECT TO_DATE(CURRENT_DATE - 1) AS yesterday_date
FROM dual;

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

Discussion

To get yesterday's date, you need to subtract one day from today. Use CURRENT_DATE to get today's date. In Oracle, you can subtract any number of days simply by subtracting that number from the current date. Here, since you need to subtract one day, you use CURRENT_DATE - 1. Then you use the TO_DATE() function to cast the result to the column type date.

You can go back by any number of days you want very easily, e.g., by seven days.

SELECT TO_DATE(CURRENT_DATE - 7) AS date_week_ago 
FROM dual;

You can also calculate a date in the future. For example, to get tomorrow's date, you add one to CURRENT_DATE:

SELECT TO_DATE(CURRENT_DATE + 1) AS tomorrow_date 
FROM dual;