ORA-01821:ISO 8601日期与本地时间的日期格式无法识别错误 [英] ORA-01821: date format not recognized error for ISO 8601 date with local time
问题描述
我正在尝试根据Java代码中的参数值在SQL中转换日期.但是,当执行以下查询时,我得到了错误.请您帮助我解决此查询.
I am trying to convert the date in SQL based on the parameter value in my Java code. However when the below query is executed I am getting error . Request you to help me in fixing this query.
SELECT TO_DATE ('2015-08-26T05:46:30.488+0100',
'YYYY-MM-DD"T"hh24:mi:ss.sTZH:TZM')
FROM DUAL
*
Error at line 2
ORA-01821: date format not recognized
日期和时间格式信息:
http://www.w3.org/TR/NOTE-datetime
推荐答案
您有两个问题:TO_DATE
无法识别任何时区成分或小数秒,您必须将其转换为带有tome时区的时间戳;而.s
仍然不是表示小数秒的方式,您需要.ff
.有效的格式模型在文档中显示.
You have two issues: TO_DATE
doesn't recognise any time zone components or fractional seconds, you'll have to convert it to a timestamp with tome zone; and .s
isn't how you represent fractional seconds anyway, you need .ff
. The valid format models are shown in the documentation.
将这些内容放在一起即可:
Putting those together you can do:
SELECT TO_TIMESTAMP_TZ ('2015-08-26T05:46:30.488+0100',
'YYYY-MM-DD"T"hh24:mi:ss.ffTZHTZM')
FROM DUAL;
TO_TIMESTAMP_TZ('2015-08-26T05:46:30.488+0100','YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZ
-------------------------------------------------------------------------------
26-AUG-15 05.46.30.488000000 +01:00
如果您确实希望将其作为日期,则需要决定如何处理时区信息-假设它是本地时间(基本上忽略它)或转换为UTC或其他时区.不过,您可能真的想将其保留为带有时区的时间戳.
If you really want it as a date you'll need to decide what to do with the time zone information - either assume it's local time (essentially ignore it) or convert to UTC, or some other time zone. You may really want to keep it as a timestamp with time zone though.
这篇关于ORA-01821:ISO 8601日期与本地时间的日期格式无法识别错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!