1 2 Previous Next 20 Replies Latest reply: Dec 14, 2010 1:47 AM by orafad RSS

    ORA-01843 not a valid month

    421909
      Hello friends, I have a problem in executing a query. See friends, I have 2 databases one is PROD and other one is TEST both having the same data like clone copy. Each database having its own different hosts. OS windows 2003 and ORACLE 10G.

      The problem is a query running in the PROD database successfully and returning required resutls. But the same query
      (as cut and paste) is not running in the TEST database showing error PRA-01843 NOT A VALID MONTH. Please note the error line below and share with me to find the soluction for the thread .

      and to_date(a.amend_last_date,'dd/mm/yyy')='17/03/2008' . I am surprising , why is not working while its working on PROD database. thanks in advance.

      Edited by: habfat on Dec 8, 2010 3:28 AM
        • 1. Re: ORA-01843 not a valid month
          Mohammed Taj
          and to_date(a.amend_last_date,'dd/mm/*y*yyy')='17/03/2008' .,

          may be given date format is not proper. "y" is missing.

          And also post the exact output with error on the forums.

          Edited by: Mohammed Taj on Dec 8, 2010 3:37 PM
          • 2. Re: ORA-01843 not a valid month
            635471
            Is a.amend_last_date a DATE column or a character type?

            The problem here is the classic one of not using Oracle dates correctly. Store dates in a date column, and compare them with dates not string literals. so your code ought to look like :
            a.amend_last_date = date '2008-03-17'
            or
            a.amend_last_date = to_date('2008-03-17','YYYY-MM-DD')
            • 3. Re: ORA-01843 not a valid month
              varun4dba
              hi,

              to_date(a.amend_last_date,'dd/mm/yyy')='17/03/2008'+

              try :--

              to_date(a.amend_last_date,'dd/mm/yyyy')='17/03/2008'

              post error what you are getting...

              these error comes when format is not correct.


              thanks.

              Edited by: varun4dba on Dec 8, 2010 5:13 PM
              • 4. Re: ORA-01843 not a valid month
                SatishKandi
                The exception could also be a result of NULL value in that column for the given (set of) row(s).
                • 5. Re: ORA-01843 not a valid month
                  421909
                  I am sorry my where condition is to_date(a.amend_last_date,'dd/mm/yyyy')='17/03/2008' .

                  the exact error is

                  and to_date(a.amend_last_date,'dd/mm/yyyy')= '17/03/2008'
                  *

                  ERROR at line 6 :
                  ORA-01843 : not a valid month.


                  Please note that its running well with prod database.But showing the above error in test database. Actually the
                  data for TEST database is populated or loaded by IMP utility.
                  • 6. Re: ORA-01843 not a valid month
                    SatishKandi
                    Please note that its running well with prod database.But showing the above error in test database. Actually the
                    data for TEST database is populated or loaded by IMP utility.
                    That does not rule out the possibility of someone making changes to the data in this column. Please cross check.
                    • 7. Re: ORA-01843 not a valid month
                      varun4dba
                      hi,

                      what is your regional settings of your server, where you are getting this error ??

                      is it same for the PROD server???

                      thanks.
                      • 8. Re: ORA-01843 not a valid month
                        SatishKandi
                        varun4dba wrote:
                        hi,

                        what is your regional settings of your server, where you are getting this error ??

                        is it same for the PROD server???

                        thanks.
                        I believe that would not matter since this is an "instance" level override (not to be confused with Oracle instance).
                        • 9. Re: ORA-01843 not a valid month
                          421909
                          hello friend, Both having the same regional setups.
                          • 10. Re: ORA-01843 not a valid month
                            orafad
                            varun4dba wrote:
                            these error comes when format is not correct.
                            Are you sure? (Wouldn't other errors such as ORA-1821 or ORA-1830 be more likely in that case.)

                            create table, inserts etc....
                            
                            SQL> select t.* from test1 t;
                            
                                    ID ALDATE
                            ---------- ----------
                                     1 01/12/0
                                     2 01/12/10
                                     3 01/12/010
                            
                            SQL> select t.*, to_date(t.aldate,'dd/mm/yyy') from test1 t;
                            
                                    ID ALDATE     TO_DATE(T.
                            ---------- ---------- ----------
                                     1 01/12/0    2000-12-01
                                     2 01/12/10   2010-12-01
                                     3 01/12/010  2010-12-01
                            No error so far. I guess the point being: we don't know the data.

                            SQL> insert into test1 values (4, '08/21/10');
                            
                            1 row created.
                            
                            SQL> select t.*, to_date(t.aldate,'dd/mm/yyy') from test1 t where id=4;
                            select t.*, to_date(t.aldate,'dd/mm/yyy') from test1 t where id=4
                                                *
                            ERROR at line 1:
                            ORA-01843: not a valid month
                            And it seems that even if format is incorrect, it might not show:
                            SQL> insert into test1 values (5, '08/21/2010');
                            
                            1 row created.
                            
                            SQL> select t.*, to_date(t.aldate,'dd/mm/yyy') from test1 t where id=5;
                            select t.*, to_date(t.aldate,'dd/mm/yyy') from test1 t where id=5
                                                *
                            ERROR at line 1:
                            ORA-01843: not a valid month
                            • 11. Re: ORA-01843 not a valid month
                              orafad
                              >
                              Please note that its running well with prod database.But showing the above error in test database. Actually the
                              data for TEST database is populated or loaded by IMP utility.
                              As said earlier, double check the data in TEST.
                              • 12. Re: ORA-01843 not a valid month
                                6363
                                habfat wrote:

                                and to_date(a.amend_last_date,'dd/mm/yyy')='17/03/2008' .
                                If the column amend_last_date is a DATE data type, I can't see anywhere in this thread where you provide this information or table description, then you have a bug in your code.

                                http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions203.htm#SQLRF06132
                                TO_DATE(char [, fmt [, 'nlsparam' ] ])
                                TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of DATE data type.
                                • 13. Re: ORA-01843 not a valid month
                                  John Spencer
                                  Assuming that amend_last_date is actually a date column, then the most likely cause is a difference in the nls_date_format parameter on the two instances.
                                  SQL> select value from v$nls_parameters
                                    2  where parameter = 'NLS_DATE_FORMAT';
                                   
                                  VALUE
                                  --------------
                                  DD-MON-FXYYYY
                                   
                                  SQL> create table t as
                                    2  select rownum id, sysdate - rownum dt
                                    3  from all_objects
                                    4  where rownum <= 10;
                                   
                                  Table created.
                                   
                                  SQL> select * from t 
                                    2  where to_date(dt, 'dd-mon-yyyy') >= '02-dec-2010';
                                   
                                          ID DT
                                  ---------- -----------
                                           1 07-DEC-2010
                                           2 06-DEC-2010
                                           3 05-DEC-2010
                                           4 04-DEC-2010
                                           5 03-DEC-2010
                                           6 02-DEC-2010
                                   
                                  SQL> alter session set nls_date_format = 'dd/mm/yyyy';
                                   
                                  Session altered.
                                   
                                  SQL> select * from t where to_date(dt, 'dd-mon-yyyy') >= '02-dec-2010';
                                  select * from t where to_date(dt, 'dd-mon-yyyy') >= '02-dec-2010'
                                                                *
                                  ERROR at line 1:
                                  ORA-01843: not a valid month
                                  What do you get from:
                                  select sysdate from dual;
                                  on each instance? Are they the same?

                                  If amend_last_date is not actually a date (and it really really should be one), then likely you have bad data in the column.

                                  John
                                  • 14. Re: ORA-01843 not a valid month
                                    421909
                                    Thanks , Mr. Its working fine. Its is very helpful to me. thanks.
                                    1 2 Previous Next