Printable View
SELECT decode(TO_CHAR(TO_DATE('11-JUN-00','DD-MON-YY'),'MONTH') ,
'FEBRUARY','02',
'JUNE', '06',
'APRIL', '04',
'JULY', '07',
'LAST OPTION' ) FROM DUAL
The expected output of the above query is 06, But it is displaying LAST OPTION. What is the wrong with above query?
Use This
select DECODE(to_char(to_date('11-JUN-00','DD-MON-YY'),'MON'),'FEB','02','JUN','06','APR','04','JULY','07','LAST OPTION') FROM DUAL
That is ok.
What it wrong with first query?
SELECT TO_CHAR(TO_DATE('11-JUN-00','DD-MON-YY'),'MONTH') from dual returns JUNE.
use this
SELECT DECODE(rtrim(TO_CHAR(TO_DATE('11-JUN-00','DD-MON-YY'),'MONTH')),
'FEBRUARY','02',
'JUNE','06',
'APRIL','04',
'JULY','07','LAST OPTION') FROM DUAL
[QUOTE=krishnaindia2007;22195]That is ok.
What it wrong with first query?
SELECT TO_CHAR(TO_DATE('11-JUN-00','DD-MON-YY'),'MONTH') from dual returns JUNE.[/QUOTE]
You need to do as suggested in the previous post because after conversion MONTH retrives month name with length of 9, padded with space to the right side because maximum month name is SEPTEMBER. The same rule applies for name of days also max being WEDNESDAY.