SQL ORA-01821：带有本地时间的 ISO 8601 日期的日期格式无法识别错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39164692/
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-01821: date format not recognized error for ISO 8601 date with local time
提问by John Liva
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.
我正在尝试根据我的 Java 代码中的参数值转换 SQL 中的日期。但是，当执行以下查询时，我收到错误消息。请求您帮助我解决此查询。
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
Date and Time format info:
日期和时间格式信息：
回答by Alex Poole
You have two issues: TO_DATEdoesn't recognise any time zone components or fractional seconds, you'll have to convert it to a timestamp with tome zone; and .sisn't how you represent fractional seconds anyway, you need .ff. The valid format models are shown in the documentation.
您有两个问题：TO_DATE无法识别任何时区组件或小数秒，您必须将其转换为带有 tome zone 的时间戳；而.s不是你如何表示分数秒无论如何，你需要.ff。文档中显示了有效的格式模型。
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
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.
如果你真的想要它作为一个日期，你需要决定如何处理时区信息 - 假设它是本地时间（基本上忽略它）或转换为 UTC，或其他时区。不过，您可能真的希望将其保留为带时区的时间戳。
回答by daZza
Well, the error message is pretty specific. Oracle does not recognize the given date format YYYY-MM-DD"T"hh24:mi:ss.sTZH:TZM.
好吧，错误消息非常具体。Oracle 无法识别给定的日期格式YYYY-MM-DD"T"hh24:mi:ss.sTZH:TZM。
You can refer to this page in order to build a proper date format: https://www.techonthenet.com/oracle/functions/to_date.php
您可以参考此页面以构建正确的日期格式：https: //www.techonthenet.com/oracle/functions/to_date.php
