Identify the Friday (or other specific weekday) before sysdate

Question

I need to return the date of the last Friday before the SYSDATE.

To be clear:

• If I ran the script today (Thursday 29th September), I would want last Friday's date to be returned (Friday 22nd September).
• If I run the script two days from now (Saturday 1st October), I would want tomorrows date to be returned (Friday 30th September).

Answer 1

Just for completeness, here's a way that's NLS-settings independent:

WITH dates AS (SELECT TRUNC(SYSDATE, 'mm') + LEVEL -1 dt
               FROM   dual
               CONNECT BY LEVEL <= 22)
SELECT dt,
       to_char(dt, 'fmDay') day_of_dt,
       TRUNC(dt + 3, 'iw') - 3  prev_fri_incl_today,
       TRUNC(dt + 2, 'iw') - 3  prev_fri_not_incl_today
FROM   dates;

DT          DAY_OF_DT PREV_FRI_INCL_TODAY PREV_FRI_NOT_INCL_TODAY
----------- --------- ------------------- -----------------------
01/09/2016  Thursday  26/08/2016          26/08/2016
02/09/2016  Friday    02/09/2016          26/08/2016
03/09/2016  Saturday  02/09/2016          02/09/2016
04/09/2016  Sunday    02/09/2016          02/09/2016
05/09/2016  Monday    02/09/2016          02/09/2016
06/09/2016  Tuesday   02/09/2016          02/09/2016
07/09/2016  Wednesday 02/09/2016          02/09/2016
08/09/2016  Thursday  02/09/2016          02/09/2016
09/09/2016  Friday    09/09/2016          02/09/2016
10/09/2016  Saturday  09/09/2016          09/09/2016
11/09/2016  Sunday    09/09/2016          09/09/2016
12/09/2016  Monday    09/09/2016          09/09/2016
13/09/2016  Tuesday   09/09/2016          09/09/2016
14/09/2016  Wednesday 09/09/2016          09/09/2016
15/09/2016  Thursday  09/09/2016          09/09/2016
16/09/2016  Friday    16/09/2016          09/09/2016
17/09/2016  Saturday  16/09/2016          16/09/2016
18/09/2016  Sunday    16/09/2016          16/09/2016
19/09/2016  Monday    16/09/2016          16/09/2016
20/09/2016  Tuesday   16/09/2016          16/09/2016
21/09/2016  Wednesday 16/09/2016          16/09/2016
22/09/2016  Thursday  16/09/2016          16/09/2016

This relies on the fact that the ISO week always starts with a Monday, and uses offsets from that to derive the previous Friday.

I've included two options - one if you want to return today's date if today is Friday, and one that returns the previous Friday.

And just for proof of the NLS settings potential issue (next_day doesn't allow you to specify the date language when calling it, unlike to_char):

SQL> ALTER SESSION SET nls_date_language = 'french';
Session altered
SQL> select next_day(trunc(sysdate), 'FRIDAY') - 7
  2  from dual;
select next_day(trunc(sysdate), 'FRIDAY') - 7
from dual
ORA-01846: not a valid day of the week

And proof that my solution still works:

SQL> WITH dates AS (SELECT TRUNC(SYSDATE, 'mm') + LEVEL -1 dt
  2                 FROM   dual
  3                 CONNECT BY LEVEL <= 22)
  4  SELECT dt,
  5         to_char(dt, 'fmDay') day_of_dt,
  6         TRUNC(dt + 3, 'iw') - 3  prev_fri_incl_today,
  7         TRUNC(dt + 2, 'iw') - 3  prev_fri_not_incl_today
  8  FROM   dates
  9  ;
DT          DAY_OF_DT PREV_FRI_INCL_TODAY PREV_FRI_NOT_INCL_TODAY
----------- --------- ------------------- -----------------------
01/09/2016  Jeudi     26/08/2016          26/08/2016
02/09/2016  Vendredi  02/09/2016          26/08/2016
03/09/2016  Samedi    02/09/2016          02/09/2016
04/09/2016  Dimanche  02/09/2016          02/09/2016
05/09/2016  Lundi     02/09/2016          02/09/2016
06/09/2016  Mardi     02/09/2016          02/09/2016
07/09/2016  Mercredi  02/09/2016          02/09/2016
08/09/2016  Jeudi     02/09/2016          02/09/2016
09/09/2016  Vendredi  09/09/2016          02/09/2016
10/09/2016  Samedi    09/09/2016          09/09/2016
11/09/2016  Dimanche  09/09/2016          09/09/2016
12/09/2016  Lundi     09/09/2016          09/09/2016
13/09/2016  Mardi     09/09/2016          09/09/2016
14/09/2016  Mercredi  09/09/2016          09/09/2016
15/09/2016  Jeudi     09/09/2016          09/09/2016
16/09/2016  Vendredi  16/09/2016          09/09/2016
17/09/2016  Samedi    16/09/2016          16/09/2016
18/09/2016  Dimanche  16/09/2016          16/09/2016
19/09/2016  Lundi     16/09/2016          16/09/2016
20/09/2016  Mardi     16/09/2016          16/09/2016
21/09/2016  Mercredi  16/09/2016          16/09/2016
22/09/2016  Jeudi     16/09/2016          16/09/2016

22 rows selected

Answer 2

Oracle has a convenient function called next_day() (see here), which does something related. It returns the next day of week after a given date. You can use this to get what you want:

select next_day(trunc(sysdate), 'FRIDAY') - 7
from dual;

Note: If today were a Friday, then this would return today's date. I'm not sure if that is what you intend. If you want the previous Friday, then use sysdate - 1.