1 2 Previous Next 18 Replies Latest reply on Nov 21, 2014 6:54 PM by jgarry

    not a valid month error

    user13168644

      HI,

      I have oracle 12.1.0.2 database enterprise running on a linux server. I have two tables , one stores the month names and another stores the year numbers. Here is the data for the first table

       

      select  FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, CALENDRICAL_EQUIVALENT

      from  fiscal_system_periods  order by 2

       

      FISCAL_SYSTEM_PERIOD_CAL_YR_EQ CALENDRICAL_EQUIVALENT   

      ---------------------------------  -------------------------

      January                        1                   
      February                       2                   
      March                          3                   
      April                          4                   
      May                            5                   
      June                           6                   
      July                           7                   
      August                         8                   
      September                      9                   
      October                        10                  
      November                       11                  
      December                       12                  
      13 Series                     

      (null)

       

       

      when I run the following query i am getting  the invalid month error

       

      select  FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, calendrical_equivalent

      from  fiscal_system_periods 

      where  calendrical_equivalent > 0 and

      to_date(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ', ' || 2014,'Month, YYYY') >= to_date('November, 2014','Month, YYYY')

       

      [Error] Script lines: 1-5 --------------------------

      ORA-01843: not a valid month 

       

      and the data types are

       

      CALENDRICAL_EQUIVALENT     NUMBER

      FISCAL_SYSTEM_PERIOD_CAL_YR_EQ VARCHAR2

       

       

      but if i run the following query,  i am not getting any error

       

      with

      periods as  (Select 'January' period, 1 cal  From Dual                          

      union all Select 'February' period , 2 cal From Dual                         

      union all Select 'March' period , 3 cal From Dual                            

      union all Select 'April' period , 4 cal From Dual                            

      union all Select 'May'  period, 5 cal From Dual                              

      union all Select 'June' period, 6 cal From Dual                             

      union all Select 'July' period, 7 cal From Dual                             

      union all Select 'August' period, 8 cal From Dual                           

      union all Select 'September' period, 9 cal  From Dual                        

      union all Select 'October' period, 10 cal From Dual                          

      union all Select 'November' period, 11 cal From Dual                         

      union all Select 'December' period, 12 cal From Dual                         

      union all Select '13 Series' period, null cal  From Dual  )

       

      select  to_date(periods.period ||', '|| 2014,'Month, YYYY') period from periods where periods.cal > 0 and  to_date(periods.period ||', '|| 2014,'Month, YYYY') = to_date('November, 2014','Month, YYYY')

       

       

      i get the result

       

      PERIOD          

      ---------------------

      11/1/2014 12:00:00 AM

       

       

       

       

      I upgraded the DB today to 12.1.0.2 from 11.2.0. yesterday . Any one knows why this started happening ?

      Thanks

        • 1. Re: not a valid month error

          post results from below

           

          DESC fiscal_system_periods



          > I have two tables , one stores the month names and another stores the year numbers.

           

          BTW above is waste of disk space.

          • 2. Re: not a valid month error
            user13168644

            SQL> DESC fiscal_system_periods

            Name                                                  Null?    Type

            ----------------------------------------------------- -------- ------------

            --------------------

            FISCAL_SYSTEM_PERIOD_ID                               NOT NULL NUMBER(18)

            FISCAL_SYSTEM_PERIOD_DESIG                            NOT NULL VARCHAR2(5)

            FISCAL_SYSTEM_PERIOD_CFY_ID                           NOT NULL NUMBER(18)

            FISCAL_SYSTEM_PERIOD_CAL_YR_EQ                        NOT NULL VARCHAR2(20)

            CALENDRICAL_EQUIVALENT                                         NUMBER(10)

            FISCAL_SYSTEM_PERIOD_ORDER                                     NUMBER(10)

            • 3. Re: not a valid month error

              do you see where your problem is from example below?

               

               

               

              SQL> select distinct to_char(hiredate,'Month') ||'<--' Month from emp order by 1;

               

               

              MONTH

              ---------------------------------------

              April    <--

              December <--

              February <--

              January  <--

              June     <--

              May      <--

              November <--

              September<--

               

               

              8 rows selected.

               

              Using month name is sub-optimal approach since depends upon NLS_LANG setting.

              It is ALWAYS better to use month number instead of month name.

              • 4. Re: Re: not a valid month error
                Deepak Mahto

                Let me post my observations!

                 

                Create Table T
                As
                with
                periods as  (Select 'January' period, 1 cal  From Dual                         
                union all Select 'February' period , 2 cal From Dual                       
                union all Select 'March' period , 3 cal From Dual                           
                union all Select 'April' period , 4 cal From Dual                           
                union all Select 'May'  period, 5 cal From Dual                             
                union all Select 'June' period, 6 cal From Dual                           
                union all Select 'July' period, 7 cal From Dual                           
                union all Select 'August' period, 8 cal From Dual                         
                union all Select 'September' period, 9 cal  From Dual                       
                union all Select 'October' period, 10 cal From Dual                         
                union all Select 'November' period, 11 cal From Dual                       
                Union All Select 'December' Period, 12 Cal From Dual                       
                Union All Select '13 Series' Period, Null Cal  From Dual)
                Select  Period,Cal
                from periods;
                

                 

                When we invoke below SQL it work fine.

                 

                select * from (select  Period, Cal,to_date(Period || ', ' || 2014,'Month, YYYY') col1 ,to_date('November, 2014','Month, YYYY') col2
                From  T
                Where  Cal > 0 );
                
                Output :
                
                PERIOD           CAL COL1     COL2   
                --------- ---------- -------- --------
                January            1 01-01-14 01-11-14 
                February           2 01-02-14 01-11-14 
                March              3 01-03-14 01-11-14 
                April              4 01-04-14 01-11-14 
                May                5 01-05-14 01-11-14 
                June               6 01-06-14 01-11-14 
                July               7 01-07-14 01-11-14 
                August             8 01-08-14 01-11-14 
                September          9 01-09-14 01-11-14 
                October           10 01-10-14 01-11-14 
                November          11 01-11-14 01-11-14 
                December          12 01-12-14 01-11-14
                
                But when we add comparison operations , it error out
                
                select * from (select  Period, Cal,to_date(Period || ', ' || 2014,'Month, YYYY') col1 ,to_date('November, 2014','Month, YYYY') col2
                From  T
                Where  Cal > 0 ) where col1 >= col2;
                
                ORA-01843: not a valid month
                01843. 00000 -  "not a valid month"
                *Cause:   
                *Action:
                

                 

                Still not sure what the problem.... it is in same context as questions posted!!

                • 5. Re: not a valid month error
                  user13168644

                  Hi ( Sorry , didn't know your name),

                  So , why is it errors out in the second instance  i.e. when the comparison operation is added .

                  Thanks

                  • 6. Re: not a valid month error

                    post results from SQL below


                    SELECT fiscal_system_period_cal_yr_eq,

                           calendrical_equivalent,

                           Length(fiscal_system_period_cal_yr_eq)

                    FROM   fiscal_system_periods

                    ORDER  BY 2;

                     

                    • 7. Re: not a valid month error
                      Deepak Mahto

                      Buddy even I am not aware!!

                       

                      I observe what while conversion on projections it is working fine..

                      but while comparison it fails!

                       

                      lets waits... expert will help us out!!

                      • 8. Re: not a valid month error
                        user13168644
                        FISCAL_SYSTEM_PERIOD_CAL_YR_EQ CALENDRICAL_EQUIVALENT LENGTH(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ)   

                        ---------------------------------  -------------------------  -----------------------------------------

                        January                        1                      7                                   
                        February                       2                      8                                   
                        March                          3                      5                                   
                        April                          4                      5                                   
                        May                            5                      3                                   
                        June                           6                      4                                   
                        July                           7                      4                                   
                        August                         8                      6                                   
                        September                      9                      9                                   
                        October                        10                     7                                   
                        November                       11                     8                                   
                        December                       12                     8                                   
                        13 Series                      (null)                
                        • 9. Re: not a valid month error
                          ddf_dba

                          user13168644 wrote:

                           

                          HI,

                          I have oracle 12.1.0.2 database enterprise running on a linux server. I have two tables , one stores the month names and another stores the year numbers. Here is the data for the first table

                           

                          select  FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, CALENDRICAL_EQUIVALENT

                          from  fiscal_system_periods  order by 2

                           

                          FISCAL_SYSTEM_PERIOD_CAL_YR_EQ CALENDRICAL_EQUIVALENT

                          ---------------------------------  -------------------------

                          January                     1                
                          February                     2                
                          March                       3                
                          April                       4                
                          May                         5                
                          June                         6                
                          July                         7                
                          August                       8                
                          September                   9                
                          October                     10                
                          November                     11                
                          December                     12                
                          13 Series                  

                          (null)

                           

                           

                          when I run the following query i am getting  the invalid month error

                           

                          select  FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, calendrical_equivalent

                          from  fiscal_system_periods

                          where  calendrical_equivalent > 0 and

                          to_date(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ', ' || 2014,'Month, YYYY') >= to_date('November, 2014','Month, YYYY')

                           

                          [Error] Script lines: 1-5 --------------------------

                          ORA-01843: not a valid month

                           

                          and the data types are

                           

                          CALENDRICAL_EQUIVALENT   NUMBER

                          FISCAL_SYSTEM_PERIOD_CAL_YR_EQ VARCHAR2

                           

                           

                          but if i run the following query,  i am not getting any error

                           

                          with

                          periods as  (Select 'January' period, 1 cal  From Dual                        

                          union all Select 'February' period , 2 cal From Dual                      

                          union all Select 'March' period , 3 cal From Dual                          

                          union all Select 'April' period , 4 cal From Dual                          

                          union all Select 'May'  period, 5 cal From Dual                            

                          union all Select 'June' period, 6 cal From Dual                          

                          union all Select 'July' period, 7 cal From Dual                          

                          union all Select 'August' period, 8 cal From Dual                        

                          union all Select 'September' period, 9 cal  From Dual                      

                          union all Select 'October' period, 10 cal From Dual                        

                          union all Select 'November' period, 11 cal From Dual                      

                          union all Select 'December' period, 12 cal From Dual                      

                          union all Select '13 Series' period, null cal  From Dual  )

                           

                          select  to_date(periods.period ||', '|| 2014,'Month, YYYY') period from periods where periods.cal > 0 and  to_date(periods.period ||', '|| 2014,'Month, YYYY') = to_date('November, 2014','Month, YYYY')

                           

                           

                          i get the result

                           

                          PERIOD        

                          ---------------------

                          11/1/2014 12:00:00 AM

                           

                           

                           

                           

                          I upgraded the DB today to 12.1.0.2 from 11.2.0. yesterday . Any one knows why this started happening ?

                          Thanks

                          No, because I am running 12.1.0.2 (on Windows) and your query works without modification:

                           

                          SQL> select * from v$version;

                           

                          BANNER                                                                               CON_ID
                          -------------------------------------------------------------------------------- ----------
                          Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
                          PL/SQL Release 12.1.0.2.0 - Production                                                    0
                          CORE    12.1.0.2.0      Production                                                                0
                          TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0
                          NLSRTL Version 12.1.0.2.0 - Production                                                    0

                           

                          SQL>
                          SQL> create table fiscal_system_periods(
                            2          fiscal_system_period_cal_yr_eq varchar2(12) not null,
                            3          calendrical_equivalent number);

                           

                          Table created.

                           

                          SQL>
                          SQL>
                          SQL> insert all
                            2  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
                            3  values('January',1)
                            4  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
                            5  values('February',2)
                            6  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
                            7  values('March',3)
                            8  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
                            9  values('April',4)
                          10  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
                          11  values('May',5)
                          12  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
                          13  values('June',6)
                          14  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
                          15  values('July',7)
                          16  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
                          17  values('August',8)
                          18  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
                          19  values('September',9)
                          20  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
                          21  values('October',10)
                          22  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
                          23  values('November',11)
                          24  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
                          25  values('December',12)
                          26  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
                          27  values('13 Series',null)
                          28  select * From dual;

                           

                          13 rows created.

                           

                          SQL>
                          SQL> commit;

                           

                          Commit complete.

                           

                          SQL>
                          SQL> select     FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, calendrical_equivalent
                            2  from  fiscal_system_periods
                            3  where  calendrical_equivalent > 0 and
                            4  to_date(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ', ' || 2014,'Month, YYYY') >= to_date('November, 2014','Month, YYYY');

                           

                          FISCAL_SYSTE CALENDRICAL_EQUIVALENT
                          ------------ ----------------------
                          November                         11
                          December                         12

                           

                          SQL>

                           

                          Have you checked your data for invalid characters?

                           

                           

                          David Fitzjarrell

                          • 10. Re: not a valid month error
                            jgarry

                            A guess:

                            add to where: calendrical_equivalent is not null

                            • 11. Re: not a valid month error
                              user13168644

                              HI Garry,

                              Unfortunately, I cant use the calendrical_equivalent is not null, because the developers have used the similar where clause in many places ( not sure how many places and where )  I don't know where .

                              Thanks

                              Feroz

                              • 12. Re: not a valid month error
                                user13168644

                                HI David,

                                Can you please run the query with the table creation script listed by user 2795339 at 12:30 AM today on your DB and see if it works . Just for a testing purpose.

                                Thanks

                                • 13. Re: not a valid month error
                                  ADG76

                                  Looks like a version specific problem. I tried same query using your data in Oracle 11g R2 with following details as I dont have 12C available with me

                                   

                                  BANNER
                                  Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
                                  PL/SQL Release 11.2.0.2.0 - Production
                                  CORE 11.2.0.2.0 Production
                                  TNS for Linux: Version 11.2.0.2.0 - Production
                                  NLSRTL Version 11.2.0.2.0 - Production

                                   

                                   

                                  The query worked fine and returned the following result set.

                                   

                                  FISCAL_SYSTEM_PERIOD_CAL_YR_EQCALENDRICAL_EQUIVALENT
                                  November11
                                  December12
                                  • 14. Re: not a valid month error
                                    Hemant K Chitale

                                    It depends on the order in which the two ANDed predicates are evaluated.

                                    If the predicate "calendrical_equivalent > 0" is evaulated first, the row for "13 series" is excluded.  So it need not consider that row for the second predicate.

                                    If the predicate "to_date(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ', ' || 2014,'Month, YYYY') >= to_date('November, 2014','Month, YYYY')" is evaluated first, it obviously fails with ORA-01843 error for the "13 series" row.

                                     

                                    Oracle may choose to evaluate any of the two predicates first when you AND them.

                                     

                                     

                                    SQL>create table fiscal_system_periods(

                                      2              fiscal_system_period_cal_yr_eq varchar2(12) not null,

                                      3              calendrical_equivalent number)

                                      4  /

                                     

                                     

                                    Table created.

                                     

                                    SQL>insert all

                                      2    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

                                      3    values('January',1)

                                      4    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

                                      5    values('February',2)

                                      6    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

                                      7    values('March',3)

                                      8    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

                                      9    values('April',4)

                                    10    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

                                    11    values('May',5)

                                    12    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

                                    13    values('June',6)

                                    14    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

                                    15    values('July',7)

                                    16    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

                                    17    values('August',8)

                                    18    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

                                    19    values('September',9)

                                    20    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

                                    21    values('October',10)

                                    22    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

                                    23    values('November',11)

                                    24    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

                                    25    values('December',12)

                                    26    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

                                    27    values('13 Series',null)

                                    28    select * From dual

                                    29  /

                                     

                                    13 rows created.

                                     

                                    SQL>select    FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, calendrical_equivalent

                                      2      from  fiscal_system_periods

                                      3      where  calendrical_equivalent > 0 and

                                      4      to_date(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ', ' || 2014,'Month, YYYY') >= to_date('November, 2014','Month, YYYY')

                                      5  /

                                     

                                    FISCAL_SYSTE CALENDRICAL_EQUIVALENT

                                    ------------ ----------------------

                                    November                        11

                                    December                        12

                                     

                                    2 rows selected.

                                     

                                    SQL>select    FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, calendrical_equivalent

                                      2      from  fiscal_system_periods

                                      3      where

                                      4      to_date(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ', ' || 2014,'Month, YYYY') >= to_date('November, 2014','Month, YYYY')

                                      5  /

                                    ERROR:

                                    ORA-01843: not a valid month

                                     

                                    no rows selected

                                     

                                    SQL>select    FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, calendrical_equivalent

                                      2      from  fiscal_system_periods

                                      3      where  calendrical_equivalent > 0

                                      4  /

                                     

                                    FISCAL_SYSTE CALENDRICAL_EQUIVALENT

                                    ------------ ----------------------

                                    January                          1

                                    February                          2

                                    March                            3

                                    April                            4

                                    May                              5

                                    June                              6

                                    July                              7

                                    August                            8

                                    September                        9

                                    October                          10

                                    November                        11

                                    December                        12

                                     

                                    12 rows selected.

                                     

                                    SQL>

                                     

                                    Hemant K Chitale

                                    1 2 Previous Next