How to get previous working date using trunc(sysdate) in oracle?

@mallory_cormier 

One way to get the previous working date using trunc(sysdate) in Oracle is to use a combination of functions such as CASE, TO_CHAR, and TRUNC. Here is an example query that demonstrates how to achieve this:

1
2
3
4
5
6
7

SELECT 
  CASE 
    WHEN TO_CHAR(TRUNC(sysdate), 'DY') = 'MON' 
    THEN TRUNC(sysdate) - 3
    ELSE TRUNC(sysdate) - 1
  END AS previous_working_date
FROM dual;

In this query:

1. TRUNC(sysdate) is used to get the current date with the time portion truncated.
2. TO_CHAR(TRUNC(sysdate), 'DY') is used to get the day of the week for the current date (e.g., MON, TUE, etc.).
3. The CASE statement checks if the current date is a Monday. If it is, then it subtracts 3 days to get the previous Friday. Otherwise, it subtracts 1 day to get the previous working date.
4. The result is returned as the previous working date.

You can adjust the logic in the query based on your specific requirements for defining a working date in your organization (e.g., weekends, holidays).