topic Re: Oracle : decode on date value in Operating System - HP-UX

Oracle : decode on date value

Nicolas Dumeige — Wed, 05 May 2004 05:13:03 GMT

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

Re: Oracle : decode on date value

Dilip Kumar_4 — Wed, 05 May 2004 05:47:06 GMT

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

Re: Oracle : decode on date value

Jean-Luc Oudart — Wed, 05 May 2004 05:57:37 GMT

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

Re: Oracle : decode on date value

Nicolas Dumeige — Wed, 05 May 2004 07:23:58 GMT

Thank you guys !