ORA-01810: format code displayed twice

Question

Why does the sql below generate error ORA-01810? I investigated the error, and I use 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' ) 

Answer 1

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

This is wrong in two ways:

1. Invalid code format

You repeated the mask in MM format twice. MM month and MI 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 part of time

00:00:00 incorrect because it throws ORA-01849 , since the hour cannot be zero, it must be from 1 to 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 the 24-hour format or leave a fraction of the time , which is 12 AM by default.

For instance,

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:

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

Answer 2

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

mm for the month
MI for a minute
You probably used YYYYMMDD HH:MI:SS .

See the list of date format models for more information.