Oracle : decode on date value

[Nicolas Dumeige]

Hello,

I need to get the last worked day, if current day is monday, then friday.

This doesn't work :

select decode( to_char(to_date('030504','DDMMRR'),'DAY'),
'MONDAY', next_day(sysdate-6,'FRIDAY'),
sysdate-1)

any idea ?

Cheers

Nicolas

[Dilip Kumar_4 · Accepted solution]

Hi Nicolas:
If you notice the output of
to_char(to_date('030504','DDMMRR'),'DAY')
is not 'MONDAY' but 'MONDAY---'.
Now if you trim the output of this like this
trim(to_char(to_date('030504','DDMMRR'),'DAY')), it will work.

HTH
Dilip

[Jean-Luc Oudart]

Something like :
select sysdate -
(DECODE(TRIM(TO_CHAR((SYSDATE - 1),'Day')), 'Sunday', 2,'Saturday', 1,0) + 1)
from dual

this will be yesterday unless yesterday is Sunday or Saturday.

Regards,
Jean-Luc

[Nicolas Dumeige]

Thank you guys !