You can't convert a day name to the date natively, because obviously its not really conclusive - do you mean the day just gone? or the next one to occur etc. So we error out
SQL> select to_date('THURSDAY','DAY') from dual;
select to_date('THURSDAY','DAY') from dual
*
ERROR at line 1:
ORA-01835: day of week conflicts with Julian dateBut if you want to match a candidate date with a day name, you could do some SQL to handle it
SQL> variable myday varchar2(10)
SQL> exec :myday := 'THURSDAY'
PL/SQL procedure successfully completed.
SQL>
SQL> with week as (
2 select sysdate-rownum+1 dy,
3 to_char(sysdate-rownum+1,'FMDAY') nm,
4 case rownum
5 when 1 then 'TODAY'
6 when 2 then 'YESTERDAY'
7 when 3 then 'DAY BEFORE YESTERDAY'
8 end txt
9 from dual
10 connect by rownum < 7
11 )
12 select * from week where nm = :myday;
DY NM TXT
--------- ------------------------------------ ------------------
25-JUL-19 THURSDAY YESTERDAY