ORA-01843 not a valid month- Comparing Dates

Question

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  

Answer 1

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

Answer 2

You are comparing a date column to a string literal. In such a case, Oracle attempts to convert your literal to a date, using the default date format. It's a bad practice to rely on such a behavior, as this default may change if the DBA changes some configuration, Oracle breaks something in a future revision, etc.

Instead, you should always explicitly convert your literal to a date and state the format you're using:

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