date conversion from DD/MM/YYYY HH:MM:SS to YYYYMM

Question

i want to convert date to some other format.

Below is the example 04/03/10 09:00:50.000000000 AMto YYYYMM

Iam not able to get this , below is the query which i used to convert.

select to_char(to_date('04/03/10 09:00:50.000000000 AM','MM/DD/YYYY HH:MM:SS AM'),'YYYYMM') from table;

Iam getting exception as below

ORA-01810: format code appears twice 01810. 00000 - "format code appears twice"

Answer 1

1. Format Code for Minutes is MI, not MM. MM is for months.
2. You are using 2-digit year. Better to use RR for this. Even better use 4-digit year.
3. TO_DATE doesn't store fractional seconds. You need to use TO_TIMESTAMP and use the FF as format code.

So, your query would be

select to_char(to_timestamp('04/03/10 09:00:50.000000000 AM','MM/DD/RR HH:MI:SS.FF9 AM'),'YYYYMM') 
from table;