Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, naveen.

Asked: July 25, 2019 - 10:22 am UTC

Last updated: July 26, 2019 - 3:57 am UTC

Version: Oracle 11c

Viewed 1000+ times

You Asked

How to get date from week days name in oracle?

EX: select to_char(sysdate-1,'DAY') yesterday,
to_char(sysdate,'DAY')today,
to_char(sysdate+1,'DAY') tommorrow from dual;

and Connor said...

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 date


But 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



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library