How to call a dynamic day in sql?-CodePudding

I have a table I want to pull all date records before the most recent Friday. I know you can use sysdate (or getdate) to pull the current day, but all the solutions to similar questions I've looked at explicitly specify the numeric day of the week in the query. Todays Thursday so the below would work, but is there a dynamic alternative to this?

SELECT *
FROM table
WHERE datefield < sysdate - 6

CodePudding user response：

next_day function might help.

SQL> with test (datum) as
  2    -- sample data; this January up to today
  3    (select trunc(sysdate, 'mm')   level - 1
  4     from dual
  5     connect by level <= 20
  6    )
  7  select to_char(datum, 'dd.mm.yyyy, dy') datum
  8  from test
  9  where datum < next_day(sysdate - 7, 'FRIDAY')
 10  order by datum;

DATUM
------------------------
01.01.2022, sat
02.01.2022, sun
03.01.2022, mon
04.01.2022, tue
05.01.2022, wed
06.01.2022, thu
07.01.2022, fri
08.01.2022, sat
09.01.2022, sun
10.01.2022, mon
11.01.2022, tue
12.01.2022, wed
13.01.2022, thu
14.01.2022, fri

14 rows selected.

SQL>

CodePudding user response：

You could use:

SELECT NEXT_DAY(TRUNC(SYSDATE) - 7, 'FRIDAY') AS last_friday
FROM   DUAL;

However, if someone tries to query the data and is using a different language then you will get an error. I.e.:

ALTER SESSION SET NLS_DATE_LANGUAGE = 'FRENCH';

SELECT NEXT_DAY(TRUNC(SYSDATE) - 7, 'FRIDAY') AS last_friday
FROM   DUAL;

Outputs:

ORA-01846: not a valid day of the week

A solution that works regardless of the language is to compare the day to the start of the ISO week (which is always a Monday):

SELECT TRUNC(SYSDATE, 'IW')
         CASE WHEN SYSDATE - TRUNC(SYSDATE, 'IW') < 5
         THEN -3
         ELSE  4
         END AS last_friday
FROM   DUAL;

Outputs (with the NLS_DATE_FORMAT set to YYYY-MM-DD HH24:MI:SS (DY)):

LAST_FRIDAY

2022-01-14 00:00:00 (FRI)

LAST_FRIDAY
2022-01-14 00:00:00 (FRI)

db<>fiddle here

Your query would be:

SELECT *
FROM table
WHERE datefield < TRUNC(SYSDATE, 'IW')
                    CASE WHEN SYSDATE - TRUNC(SYSDATE, 'IW') < 5
                    THEN -3
                    ELSE  4
                    END