Identify the day and get the previous date in Oracle SQL-CodePudding

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 ?

CodePudding user response：

If you want to do it so that the query will work in any language and/or territory then you can compare the date to the start of the ISO week:

SELECT TO_CHAR(SYSDATE, 'DD.MM.YYYY') AS today,
       CASE TRUNC(SYSDATE) - TRUNC(SYSDATE, 'IW')
       WHEN 0 THEN TO_CHAR(SYSDATE - 3, 'DD.MM.YYYY') -- Monday
       WHEN 6 THEN TO_CHAR(SYSDATE - 2, 'DD.MM.YYYY') -- Sunday
              ELSE TO_CHAR(SYSDATE - 1, 'DD.MM.YYYY') -- Any other day
       END AS previous_weekday
FROM   DUAL;

db<>fiddle here

CodePudding user response：

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.