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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-08-13 06:58:49  来源:igfitidea点击:

Oracle Prepared Statement - ORA-01821: date format not recognized

javasqldateoracle11gwildcard

提问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')