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...
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.
Sophia analyzes international trade, startup ecosystems, retail transformation, and supply chain logistics for modern digital publications.
