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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt   09-12-23      VS   Oracle SQL Developer statement    select count    from some table where DATE TIME CREATED  lt  TO DATE  09-12-23   RR-MM-DD

User · Answer

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  gt     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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

ALTER session set NLS LANGUAGE    AMERICAN

[sql] ORA-01843 not a valid month- Comparing Dates

Examples related to sql

Examples related to oracle

Examples related to date

Examples related to select