How to Get Last Friday Date in SQL
In SQL, working with dates is a common task that often requires extracting specific dates or calculating date ranges. One common scenario is when you need to find the date of the last Friday. This could be for various reasons, such as generating reports, scheduling tasks, or simply keeping track of time. In this article, we will explore different methods to retrieve the last Friday date in SQL for various database systems, including MySQL, PostgreSQL, SQL Server, and Oracle.
MySQL
MySQL provides a straightforward way to get the last Friday date using the `LAST_DAY` function combined with `DATE_SUB`. The `LAST_DAY` function returns the last day of the month for a given date, and `DATE_SUB` subtracts a specified time interval from a date. Here’s an example:
“`sql
SELECT DATE_SUB(LAST_DAY(CURDATE()), INTERVAL 1 DAY) AS last_friday;
“`
This query calculates the last day of the current month using `LAST_DAY(CURDATE())` and then subtracts one day to get the last Friday.
PostgreSQL
In PostgreSQL, you can use the `CURRENT_DATE` function along with `EXTRACT` and `INTERVAL` to achieve the same result. The `EXTRACT` function allows you to extract parts of a date, and `INTERVAL` is used to specify a time interval. Here’s an example:
“`sql
SELECT CURRENT_DATE – INTERVAL ‘1 day’ (EXTRACT(DOW FROM CURRENT_DATE) – 5) AS last_friday;
“`
This query calculates the last Friday by subtracting one day for each day of the week before Friday (5 days in this case) from the current date.
SQL Server
SQL Server provides the `DATEADD` and `DATEDIFF` functions to calculate the last Friday date. The `DATEADD` function adds a specified time interval to a date, and `DATEDIFF` returns the difference between two dates. Here’s an example:
“`sql
SELECT DATEADD(day, DATEDIFF(day, 0, CAST(GETDATE() AS date)) – 5, 0) AS last_friday;
“`
This query calculates the difference between the current date and the first day of the week (0), subtracts 5 days to get to Friday, and then adds 0 to reset the time part of the date.
Oracle
Oracle offers the `ADD_MONTHS` and `LAST_DAY` functions to find the last Friday date. The `ADD_MONTHS` function adds a specified number of months to a date, and `LAST_DAY` returns the last day of the month. Here’s an example:
“`sql
SELECT ADD_MONTHS(LAST_DAY(SYSDATE), -1) – INTERVAL ‘5’ DAY AS last_friday;
“`
This query calculates the last day of the previous month using `ADD_MONTHS(LAST_DAY(SYSDATE), -1)` and then subtracts 5 days to get the last Friday.
Conclusion
In this article, we have discussed various methods to retrieve the last Friday date in SQL for different database systems. By utilizing the functions and features provided by each database, you can easily calculate the last Friday date for your specific needs. Whether you are working with MySQL, PostgreSQL, SQL Server, or Oracle, these methods will help you achieve the desired result.
