You Asked
Hello
I have doubt on decode function
I am running below query
select decode(UPPER(to_char(next_day(sysdate,'SATURDAY'),'DAY')) ,
'SATURDAY',sysdate -10,
'SUNDAY',sysdate-9,
'MONDAY',sysdate-8,sysdate) as x from dual
This decode is going to default value which sysdate.
The expression of the decode returns 'SATURDAY' so I am expecting to give sysdate-10.
If I use below query it give expected result
select decode(to_char(next_day(sysdate,'SATURDAY'),'D') ,
7,sysdate -10,
1,sysdate-9,
2,sysdate-8,sysdate) as x from dual
Can you help me to understand why it is happening? I want to use the first query
Regards,
Stuti
and Chris said...
Ack, those pesky format masks for to_char!
DAY right pads with spaces up to nine characters. So the decode compares:
'SATURDAY ' = 'SATURDAY'
Which returns false! To avoid this, either use the FM modifier or trim the output:
select length(to_char ( next_day ( sysdate,'SATURDAY' ) ,'DAY' )) len,
decode ( upper ( to_char ( next_day ( sysdate,'SATURDAY' ) ,'fmDAY' ) ) ,
'SATURDAY',sysdate - 10,
'SUNDAY',sysdate-9,
'MONDAY',sysdate-8,sysdate ) as fm,
decode ( trim(upper ( to_char ( next_day ( sysdate,'SATURDAY' ) ,'DAY' )) ) ,
'SATURDAY',sysdate - 10,
'SUNDAY',sysdate-9,
'MONDAY',sysdate-8,sysdate ) as trimmed,
decode ( trim(upper ( to_char ( next_day ( sysdate,'SATURDAY' ) ,'DAY' )) ) ,
'SATURDAY',sysdate - 10,
'SUNDAY',sysdate-9,
'MONDAY',sysdate-8,sysdate ) as padded
from dual;
LEN FM TRIMMED PADDED
9 21-AUG-2016 08:33:04 21-AUG-2016 08:33:04 21-AUG-2016 08:33:04
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment