ORA-01843不是有效的月份 - 比较日期 [英] ORA-01843 not a valid month- Comparing Dates

问题描述

当我们尝试从按日期筛选的表格中选择数据时，我遇到问题。例如：

I have a problem when try to select data from a table filtering by date. For example:

SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = '23 / 04/49';

SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = '23/04/49';

Oracle错误是：

The Oracle Error is:

Informe de error:
Error SQL: ORA-01843: mes no válido
01843. 00000 -  "not a valid month"
*Cause:    
*Action:

可能表的源数据已损坏，在这种情况下：我如何解决这个问题？
我可以将此日期更改为null吗？

Probably the source data of table is corrupted, in this case: How can i solve this problem? Can i change this dates for null?

此选择的结果，从nls_session_parameters中选择* ，是：

The results of this select, select * from nls_session_parameters; , is:

PARAMETER                      VALUE                                  
------------------------------ ----------------------------------------
NLS_LANGUAGE                   SPANISH                                  
NLS_TERRITORY                  SPAIN                                    
NLS_CURRENCY                   ¿                                        
NLS_ISO_CURRENCY               SPAIN                                    
NLS_NUMERIC_CHARACTERS         ,.                                       
NLS_CALENDAR                   GREGORIAN                                
NLS_DATE_FORMAT                DD/MM/RR                                 
NLS_DATE_LANGUAGE              SPANISH                                  
NLS_SORT                       SPANISH                                  
NLS_TIME_FORMAT                HH24:MI:SSXFF                            
NLS_TIMESTAMP_FORMAT           DD/MM/RR HH24:MI:SSXFF                   
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR                        
NLS_TIMESTAMP_TZ_FORMAT        DD/MM/RR HH24:MI:SSXFF TZR               
NLS_DUAL_CURRENCY              ¿                                        
NLS_COMP                       BINARY                                   
NLS_LENGTH_SEMANTICS           BYTE                                     
NLS_NCHAR_CONV_EXCP            FALSE 

谢谢！

推荐答案

你应使用to_date函数 http://www.techonthenet.com/oracle/functions/to_date.php

You should use the to_date function http://www.techonthenet.com/oracle/functions/to_date.php

SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = TO_DATE('23/04/49', 'DD/MM/YY');

这篇关于ORA-01843不是有效的月份 - 比较日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！