How to Get Yesterday Date in SQL Query
In SQL, retrieving the date of the previous day is a common task that can be achieved using various functions depending on the SQL database you are working with. Whether you are using MySQL, PostgreSQL, SQL Server, or Oracle, there are specific functions and methods to fetch the date of yesterday. In this article, we will explore different ways to get yesterday’s date in SQL queries for different database systems.
MySQL
In MySQL, you can use the `CURDATE()` function to get the current date and then subtract one day from it using the `INTERVAL` keyword. Here’s an example:
“`sql
SELECT CURDATE() – INTERVAL 1 DAY AS yesterday_date;
“`
This query will return the date of yesterday.
PostgreSQL
PostgreSQL offers the `current_date` function to get the current date. To get yesterday’s date, you can subtract one day from `current_date`:
“`sql
SELECT current_date – INTERVAL ‘1 day’ AS yesterday_date;
“`
This will give you the date of yesterday.
SQL Server
In SQL Server, you can use the `DATEADD()` function to subtract one day from the current date. Here’s how you can do it:
“`sql
SELECT DATEADD(day, -1, CAST(GETDATE() AS date)) AS yesterday_date;
“`
This query will return the date of yesterday.
Oracle
Oracle provides the `SYSDATE` function to get the current date and time. To get yesterday’s date, you can use the `TRUNC()` function to truncate the time part and subtract one day:
“`sql
SELECT TRUNC(SYSDATE – INTERVAL ‘1’ DAY) AS yesterday_date FROM dual;
“`
This query will return the date of yesterday.
Conclusion
In conclusion, getting yesterday’s date in SQL queries is a straightforward task that can be accomplished using the appropriate functions and methods provided by different SQL database systems. By understanding the functions available in your specific database, you can easily retrieve the date of the previous day and incorporate it into your SQL queries.
