[sql] 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';

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 

ALTER session set NLS_LANGUAGE=’AMERICAN’;

If you are using command line tools, then you can also set it in the shell.

On linux, with a sh type shell, you can do for example:

export NLS_TIMESTAMP_FORMAT='DD/MON/RR HH24:MI:SSXFF'

Then you can use the command line tools and it will use the specified format:

/path/to/dbhome_1/bin/sqlldr user/pass@host:port/service control=table.ctl direct=true

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

Just in case this helps, I solved this by checking the server date format:

SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT';

then by using the following comparison (the left field is a date+time):

AND EV_DTTM >= ('01-DEC-16')

I was trying this with TO_DATE but kept getting an error. But when I matched my string with the NLS_DATE_FORMAT and removed TO_DATE, it worked...

I know this is a bit late, but I'm having a similar issue. SQL*Plus executes the query successfully, but Oracle SQL Developer shows the ORA-01843: not a valid month error.

SQL*Plus seems to know that the date I'm using is in the valid format, whereas Oracle SQL Developer needs to be told explicitly what format my date is in.

• SQL*Plus statement:

  select count(*) from some_table where DATE_TIME_CREATED < '09-12-23';
  

VS

• Oracle SQL Developer statement:

   select count(*) from some_table where DATE_TIME_CREATED < TO_DATE('09-12-23','RR-MM-DD');
  

If you don't need to check exact timestamp, use

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

otherwise, you can use

SELECT * FROM MYTABLE WHERE DATEIN = TO_DATE('23-04-49 20:18:07','DD-MM-YY HH24:MI:SS');

Here, you use hard code date,if you directly compare then you must use DD-MM-YY HH24:MI:SS else you might get ORA-01849: hour must be between 1 and 12.

In a comment to one of the answers you mention that to_date with a format doesn't help. In another comment you explain that the table is accessed via DBLINK.

So obviously the other system contains an invalid date that Oracle cannot accept. Fix this in the other dbms (or whatever you dblink to) and your query will work.

Having said this, I agree with the others: always use to_date with a format to convert a string literal to a date. Also never use only two digits for a year. For example '23/04/49' means 2049 in your system (format RR), but it confuses the reader (as you see from the answers suggesting a format with YY).

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

If the source date contains minutes and seconds part, your date comparison will fail. you need to convert source date to the required format using to_char and the target date also.