mon-Fri

select to_char(sysdate, 'Day') from dual;

select to_char(to_date('18-Aug-17','DD-Mon-YY'), 'Day') from dual;

select decode(trim(to_char(to_date(:DAT,'DD-Mon-YY'), 'Day')),'Monday',trim(to_char(to_date(:DAT,'DD-Mon-YY')-3, 'Day')),trim(to_char(to_date(:DAT,'DD-Mon-YY')-1, 'Day'))) DAY from dual;


select decode(to_char(to_date('14-Aug-17','DD-Mon-YY'), 'Day'),'Monday','A','B') DAY from dual;

select decode(trim(to_char(to_date('18-Aug-17','DD-Mon-YY'), 'Day')),'Friday','A','B') DAY from dual;

select * from oe_order_headers_all;

select  Ordered_date,
        trim(to_char(to_date(Ordered_date,'DD-Mon-YY'), 'Day'))ACTUAL_DAY,
        decode(trim(to_char(to_date(ORDERED_DATE,'DD-Mon-YY'), 'Day')),'Monday',trim(to_char(to_date(ORDERED_DATE,'DD-Mon-YY')-3, 'Day')),trim(to_char(to_date(ORDERED_DATE,'DD-Mon-YY')-1, 'Day'))) DAY 
        --,trim(to_char(to_date(Ordered_date,'DD-Mon-YY'), 'D'))DayNUM  -- To Sort in week day wise
from    oe_order_headers_all 
where   1=1
and   trim(to_char(to_date(Ordered_date,'DD-Mon-YY'), 'Day')) not in ('Sunday','Saturday')
--order by DayNUM;-- To Sort in week day wise
;
--Weekely once every friday
select  Ordered_date,
        trim(to_char(to_date(Ordered_date,'DD-Mon-YY'), 'Day'))ACTUAL_DAY,
        decode(trim(to_char(to_date(ORDERED_DATE,'DD-Mon-YY'), 'Day')),'Friday',trim(to_char(to_date(ORDERED_DATE,'DD-Mon-YY')-3, 'Day')) ,trim(to_char(to_date(ORDERED_DATE,'DD-Mon-YY')-1, 'Day'))) DAY 
        --,trim(to_char(to_date(Ordered_date,'DD-Mon-YY'), 'D'))DayNUM  -- To Sort in week day wise
from    oe_order_headers_all 
where   1=1
and   trim(to_char(to_date(Ordered_date,'DD-Mon-YY'), 'Day')) not in ('Sunday','Saturday')
--order by DayNUM;-- To Sort in week day wise
;

select distinct trim(to_char(to_date(ORDERED_DATE,'DD-Mon-YY'), 'Day')) 
from oe_order_headers_all 
where trim(to_char(to_date(ORDERED_DATE,'DD-Mon-YY'), 'Day')) between 'Monday' and 'Friday';