Identify the day and get the previous date in Oracle SQL

Question

I have below query which gives current date. I want to return the value as String for this reason i used TO_CHAR.

select NVL(TO_CHAR(sysdate,'DD.MM.YYYY'),0) from dual

But i need to identify Day and based on this it should return the previous Date. For example when the query runs on every Monday it should return the date from last Friday. When the query runs from Tuesday till Friday it should return the date from previous day.

For example when the query runs today it should return the date from last Friday i.e 18.02.2022. When the query runs tommorow it should return the date from Today 21.02.2022.

I want to avoid dates from every Saturday and Sunday. Can we do this in one query ?

Answer 1

As a slight variation on MTO's answer, just to perhaps make it clearer to a future maintainer, you could use day names or abbreviations instead - but would need to specify the date language (which maybe assumes the hypothetical future maintainer uses, or at least understands, that language):

select to_char(sysdate
    - case to_char(sysdate, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH')
        when 'MON' then 3
        when 'SUN' then 2
        else 1
      end, 'DD.MM.YYYY') as result
from dual

RESULT
----------
18.02.2022

db<>fiddle, including what you see for a range of 14 days, not just today.