• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • Last Modified:

How to get previous week friday based on sysdate.

How to get previous week friday based on sysdate.  We are planning to include this logic on a job that runs daily.

Please advise !
0
d27m11y
Asked:
d27m11y
1 Solution
 
slightwv (䄆 Netminder) Commented:
Try this:
select next_day(sysdate-7,'FRIDAY') from dual;
1
 
johnsoneSenior Oracle DBACommented:
If the current day is Friday, what do you want returned.  Let's say that today is 10/07/2016.  Should it return 10/07/2016 or 09/30/2016?

If the current day is a Friday, sysdate-7 will return today, not last Friday.  If you truly want last Friday, you have to do sysdate-8.

This will show the difference between minus 7 and minus 8:
WITH x 
     AS (SELECT To_date('10022016', 'mmddyyyy') + LEVEL - 1 dt 
         FROM   dual 
         CONNECT BY LEVEL < 8) 
SELECT dt, 
       Next_day(dt - 7, 'FRIDAY') minus7, 
       Next_day(dt - 8, 'FRIDAY') minus8 
FROM   x; 

Open in new window

Notice that the difference is on the Friday (10/07).
1

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now