gpt4 book ai didi

sql - ORA-01810 : format code appears twice

转载 作者:行者123 更新时间:2023-12-03 11:32:18 25 4
gpt4 key购买 nike

为什么下面的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'
)

最佳答案

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



它有两个方面的错误:

1.格式码不正确

您重复了 MM两次格式化掩码。 MMMI分钟 .
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

无时间部分:
SQL> SELECT  TO_TIMESTAMP('20151206', 'yyyymmdd') my_tmstamp FROM dual;

MY_TMSTAMP
-----------------------------------------------------------------------
06-DEC-15 12.00.00.000000000 AM

关于sql - ORA-01810 : format code appears twice,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/34213502/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com