ORA-01810: format code appears twice

Why is the sql below generating an ORA-01810 error? I researched the error and I am using different date formats for each date insert

INSERT INTO bag_grte_clm
( schd_dprt_ldt, arr_trpn_stn_cd, bkg_crtn_gdt, sbmt_bag_grte_clm_dt, bag_grte_clm_stt_cd, lst_updt_gts, bag_grte_clm_gts, dprt_trpn_stn_cd
)
VALUES ( TO_DATE('2015/12/06', 'yyyy/mm/dd'), 'YUL', TO_DATE('2015-11-15', 'yyyy-mm-dd'), TO_DATE('120615', 'MMDDYY'), 'DENIAL', (current_timestamp), TO_TIMESTAMP('20151206 00:00:00', 'yyyymmdd hh:mm:ss'), 'ATL'
) 

2 Answers

TO_TIMESTAMP('20151206 00:00:00', 'yyyymmdd hh:mm:ss')

It is wrong in two ways:

1. Incorrect format code

You have repeated the MM format mask twice. MM is month and MI is minutes.

SQL> SELECT TO_TIMESTAMP('20151206 00:00:00', 'yyyymmdd hh:mm:ss') FROM dual;
SELECT TO_TIMESTAMP('20151206 00:00:00', 'yyyymmdd hh:mm:ss') FROM dual *
ERROR at line 1:
ORA-01810: format code appears twice

2. Incorrect time portion

00:00:00 is wrong as it would throw ORA-01849 since the hour cannot be zero, it must be between 1 and 12.

SQL> SELECT TO_TIMESTAMP('20151206 00:00:00', 'yyyymmdd hh:mi:ss') FROM dual;
SELECT TO_TIMESTAMP('20151206 00:00:00', 'yyyymmdd hh:mi:ss') FROM dual *
ERROR at line 1:
ORA-01849: hour must be between 1 and 12

The correct way is to either use 24 hour format, or leave the time portion which would default to 12 AM.

For example,

24 hour format:

SQL> SELECT TO_TIMESTAMP('20151206 00:00:00', 'yyyymmdd hh24:mi:ss') my_tmstamp FROM dual;
MY_TMSTAMP
---------------------------------------------------------------------------
06-DEC-15 12.00.00.000000000 AM

No time portion:

SQL> SELECT TO_TIMESTAMP('20151206', 'yyyymmdd') my_tmstamp FROM dual;
MY_TMSTAMP
-----------------------------------------------------------------------
06-DEC-15 12.00.00.000000000 AM

You've used the mm format code twice in TO_TIMESTAMP('20151206 00:00:00', 'yyyymmdd hh:mm:ss')

MM is for month
MI is for minute
You probably meant to use YYYYMMDD HH:MI:SS.

Please see the list of date format models for more information.