SQL ORA-01810: 格式代码出现两次
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34213502/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-01810: format code appears twice
提问by LedMan1001
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
为什么下面的sql会产生ORA-01810错误？我研究了错误，我为每个日期插入使用了不同的日期格式
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'
)
回答by Lalit Kumar B
TO_TIMESTAMP('20151206 00:00:00', 'yyyymmdd hh:mm:ss')
TO_TIMESTAMP('20151206 00:00:00', 'yyyymmdd hh:mm:ss')
It is wrong in two ways:
它有两个方面的错误：
1. Incorrect format code
1. 格式代码不正确
You have repeated the MMformat mask twice. MMis monthand MIis minutes.
您已重复MM两次格式掩码。MM是月，MI是分钟。
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
2. 时间部分不正确
00:00:00is wrong as it would throw ORA-01849since the hour cannot be zero, it must be between 1 and 12.
00:00:00是错误的，因为它会抛出ORA-01849，因为小时不能为零，它必须在 1 和 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 hourformat, or leave the time portionwhich would default to 12 AM.
正确的方法是要么使用24小时格式，或离开时间部分这将默认为12 AM。
For example,
例如，
24 hour format:
24 小时制：
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
回答by Mr. Llama
You've used the mmformat code twice in TO_TIMESTAMP('20151206 00:00:00', 'yyyymmdd hh:mm:ss')
您已经mm两次使用格式代码TO_TIMESTAMP('20151206 00:00:00', 'yyyymmdd hh:mm:ss')
MMis for monthMIis for minute
You probably meant to use YYYYMMDD HH:MI:SS.
MM是月份MI是分钟
您可能打算使用YYYYMMDD HH:MI:SS.
Please see the list of date format modelsfor more information.
请参阅日期格式模型列表以获取更多信息。
