ORA-01843 不是有效月份 - 比较日期

尝试从按日期过滤的表中选择数据时遇到问题.

例如:

<块引用>

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

Oracle 错误是:

<块引用>

通知错误:错误 SQL: ORA-01843: mes no válido01843. 00000 - 无效月份"*原因:*行动:

可能是表的源数据损坏了，在这种情况下:

• 我该如何解决这个问题?
• 我可以将此日期更改为空吗?

这个select的结果，select * from nls_session_parameters;，是:

参数值------------------------------ -----------------------------------------NLS_LANGUAGE 西班牙语NLS_TERRITORY 西班牙NLS_CURRENCY ¿NLS_ISO_CURRENCY 西班牙NLS_NUMERIC_CHARACTERS ，.NLS_CALENDAR 格里高利NLS_DATE_FORMAT DD/MM/RRNLS_DATE_LANGUAGE 西班牙语NLS_SORT 西班牙文NLS_TIME_FORMAT HH24:MI:SSXFFNLS_TIMESTAMP_FORMAT DD/MM/RR HH24:MI:SSXFFNLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZRNLS_TIMESTAMP_TZ_FORMAT DD/MM/RR HH24:MI:SSXFF TZRNLS_DUAL_CURRENCY ¿NLS_COMP 二进制NLS_LENGTH_SEMANTICS BYTENLS_NCHAR_CONV_EXCP FALSE

解决方案

您应该使用 to_date 函数 (oracle/functions/to_date.php)

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

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';

The Oracle Error is:

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

Probably the source data of table is corrupted, in this case:

• How can i solve this problem?
• Can I change this dates for null?

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 

解决方案

You should use the to_date function (oracle/functions/to_date.php )

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