how to convert date in oracle

Question

I have a date in the format of:

27-MAY-09 12.00.00.000000 AM

I want to convert it to:

05/27/2009

I did to_char(my_date_variable, 'MM/DD/YYYY') however that gives me character to number conversion error

what can I do to convert this date?

my_date_variable is declared as:

my_date_variable VARCHAR2(40);

Answer 1

You must first convert my_date_variable from VARCHAR2 to TIMESTAMP:

to_char(to_timestamp(my_date_variable), 'MM/DD/YYYY')