Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle : decode on date value

SOLVED
Go to solution
Nicolas Dumeige
Esteemed Contributor

Oracle : decode on date value

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
All different, all Unix
3 REPLIES
Solution

Re: Oracle : decode on date value

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
Honored Contributor

Re: Oracle : decode on date value

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
fiat lux
Nicolas Dumeige
Esteemed Contributor

Re: Oracle : decode on date value

Thank you guys !
All different, all Unix