Java Oracle 准备好的语句 - ORA-01821:无法识别日期格式
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/21156431/
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
Oracle Prepared Statement - ORA-01821: date format not recognized
提问by Mark McK
I am currently trying to insert dates into my local Oracle database, the insert is written within a java JAR file which uses wildcards. The INSERT works if I send the dates as null however when I attempt to send a date I get the following error:
我目前正在尝试将日期插入到我的本地 Oracle 数据库中,插入是在使用通配符的 java JAR 文件中编写的。如果我将日期发送为空,则 INSERT 有效,但是当我尝试发送日期时,我收到以下错误:
ORA-01821: date format not recognized
I have ran two SQL commands:
我已经运行了两个 SQL 命令:
SELECT PARAMETER, VALUE FROM v$nls_parameters;
SELECT CURRENT_TIMESTAMP FROM DUAL;
Which return this data:
返回此数据:
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE ENGLISH
NLS_TERRITORY UNITED KINGDOM
NLS_CURRENCY £
NLS_ISO_CURRENCY UNITED KINGDOM
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE ENGLISH
NLS_CHARACTERSET AL32UTF8
NLS_SORT BINARY
NLS_TIME_FORMAT HH24.MI.SSXFF
NLS_TIMESTAMP_FORMAT DD-MON-RR HH24.MI.SSXFF
NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH24.MI.SSXFF TZR
NLS_DUAL_CURRENCY
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
19 rows selected
CURRENT_TIMESTAMP
------------------------------------
16-JAN-14 08.03.06.437000000 EUROPE/
LONDON
After looking at the data above I attempted to amend my prepared statement to match the timestamps shown:
查看上面的数据后,我尝试修改我准备好的语句以匹配显示的时间戳:
TO_TIMESTAMP(?,'DD-MON-RR HH24.MI.SSXFF TZR')
TO_TIMESTAMP(?,'DD-MON-RR HH24.MI.SSXFF')
Where ? is the wildcard value that I am passing in using java i.e. interaction.getStartDate();
在哪里 ?是我使用 java 传递的通配符值,即interaction.getStartDate();
Does anyone know how to find the correct format or where I am going wrong? I have looked online for the last couple of days and am completely baffled.
有谁知道如何找到正确的格式或我哪里出错了?我最近几天在网上查看,完全感到困惑。
采纳答案by Mark McK
I got the date to store into the database correctly after some tests. The date format that I used in the TO_TIMESTAMP call was 'DD-Mon-RRRR HH:MI:SS PM', full insert shown below:
经过一些测试,我得到了正确存储到数据库中的日期。我在 TO_TIMESTAMP 调用中使用的日期格式是“DD-Mon-RRRR HH:MI:SS PM”,完整插入如下所示:
TO_TIMESTAMP(?,'DD-Mon-RRRR HH:MI:SS PM')