How to call a dynamic day in sql?

Solution 1:

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)

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