I would like to convert the following date to this format(DD-MON-YYYY).
I tried executing the below query but I got the error saying "date format not recognised".
select to_char(to_date('Sat Dec 01 00:00:00 IST 2012','EEE Mon dd HH:mm:ss z yyyy'),'DD-MON-YYYY') from dual;
For everything but the time zone:
'DY MON DD HH24:MI:SS YYYY'
For timezone support you need to use a conversion function that supports a timezone like TO_TIMESTAMP_TZ() and have the time zone name as one Oracle recognizes in the form it recognizes.
select to_char( TO_TIMESTAMP_TZ(
REPLACE( 'Sat Dec 01 21:00:00 IST 2012','IST','Asia/Calcutta'),
'DY MON DD HH24:MI:SS TZR YYYY'),'DD-MON-YYYY') from dual;
The relation between time zone names and abbreviations is one to many for most.
SELECT tzname, tzabbrev FROM v$timezone_names where TZABBREV = 'IST'
For your example it would probably be easier to remove some or all of the date parts you don't need in the output before conversion.
select to_char( to_date( replace('Sat Dec 01 21:00:00 IST 2012','IST',''),
'DY MON DD HH24:MI:SS YYYY'),'DD-MON-YYYY') from dual;
dualto me would indicate its Oracle