14 Replies Latest reply: Apr 9, 2009 8:52 PM by SanjayRs RSS

    ORA-01843 : NOT A VALID MONTH ERROR

    jjk
      Helllo eveyone,
      I'm using Oracle 9i and get the "not a valid month" error when executing the below quer

      SELECT to_date(to_char(SYSDATE, 'dd/mm/yyyy HH:MI:SS')) FROM DUAL

      Please tell me why it's giving me this error

      regards,
      rossy_rocs
        • 1. Re: ORA-01843 : NOT A VALID MONTH ERROR
          638677
          Try this one
          The problem is because of the format mask.
          Since the string is not in the default date format,you should explicitly give format mask for to_date like:
          SELECT to_date(to_char(SYSDATE, 'dd/mm/yyyy HH:MI:SS'),'dd/mm/yyyy HH:MI:SS') FROM DUAL;
          Edited by: Sreekanth Munagala on Apr 9, 2009 6:32 AM
          • 2. Re: ORA-01843 : NOT A VALID MONTH ERROR
            SomeoneElse
            It's because your NLS date format, doesn't match what you have in your to_char.

            But why are you doing this in the first place?

            Why take a date, convert it into a character string, then back into a date?
            • 3. Re: ORA-01843 : NOT A VALID MONTH ERROR
              6363
              Why are you converting a date to a string and back to a date again?

              If this worked it would be exactly the same as
              select sysdate from dual;
              • 4. Re: ORA-01843 : NOT A VALID MONTH ERROR
                Solomon Yakobson
                Well, your query does not make much sense. It converst DATE (SYSDATE) to string and back to DATE - it moves air. Now error explanation. You converrt SYSDATE to string in 'dd/mm/yyyy HH:MI:SS' format. Then yoy convert that string back to date without specifying date format. So Oracle uses your session default date format, which most likely is DD-MON-YY. So no wonder you are getting the error.

                SY.
                • 5. Re: ORA-01843 : NOT A VALID MONTH ERROR
                  Kamran Agayev A.
                  You should write
                  SELECT to_date(to_char(SYSDATE, 'dd/mm/yyyy HH:MI:SS'),'dd/mm/yyyy HH:MI:SS') FROM DUAL
                  - - - - - - - - - - - - - - - - - - - - -
                  Kamran Agayev A. (10g OCP)
                  http://kamranagayev.wordpress.com
                  • 6. Re: ORA-01843 : NOT A VALID MONTH ERROR
                    6363
                    Which can be written more simply as
                    select sysdate from dual;
                    • 7. Re: ORA-01843 : NOT A VALID MONTH ERROR
                      jjk
                      Actually,
                      In a query I'm comparing a date column with yesterday's and that column is having an index.
                      Applying trunc increases the cost. So I've tried to convert the yesterday's date (sysdate-1) to the format as in the column

                      The date in the column is in dd/mm/yyyy hh:mi:ss format
                      e.g. *09/04/2009 19:11:06*

                      Hope this clarifies the scenario

                      regards,
                      rossy_rocs
                      • 8. Re: ORA-01843 : NOT A VALID MONTH ERROR
                        Solomon Yakobson
                        rossy_rocs wrote:
                        Applying trunc increases the cost. So I've tried to convert the yesterday's date (sysdate-1) to the format as in the column
                        WHERE your_date_column >= TRUNC(SYSDATE) - 1 AND your_date_column < TRUNC(SYSDATE)
                        will select all yesterday dates and will use index.

                        SY.
                        • 9. Re: ORA-01843 : NOT A VALID MONTH ERROR
                          6363
                          The date in the column is in dd/mm/yyyy hh:mi:ss format
                          No it isn't if it is a date.

                          If trunc is causing it to not use an index, why do you think to_char and to_date won't?

                          try
                              mydate >= trunc(sysdate) - 1
                          and mydate < trunc(sysdate)
                          • 10. Re: ORA-01843 : NOT A VALID MONTH ERROR
                            Kamran Agayev A.
                            It depends on NLS_DATE_FORMAT parameter
                            Look at this example
                            SQL> select value  from v$nls_parameters where parameter='NLS_DATE_FORMAT';
                            DD-MON-RR
                            
                            SQL> select sysdate from dual;
                            09-APR-09
                            
                            SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
                            
                            Session altered.
                            
                            SQL> select sysdate from dual;
                            09/04/2009 18:43:59
                            
                            SQL>
                            - - - - - - - - - - - - - - - - - - - - -
                            Kamran Agayev A. (10g OCP)
                            http://kamranagayev.wordpress.com
                            • 11. Re: ORA-01843 : NOT A VALID MONTH ERROR
                              6363
                              And the difference between
                              SELECT to_date(to_char(SYSDATE, 'dd/mm/yyyy HH:MI:SS'),'dd/mm/yyyy HH:MI:SS') FROM DUAL
                              and
                              select sysdate from dual;
                              Is what exactly?
                              SQL> select value  from v$nls_parameters where parameter='NLS_DATE_FORMAT';
                              
                              VALUE
                              ----------------------------------------------------------------
                              DD-MON-RR
                              
                              SQL> select sysdate from dual;
                              
                              SYSDATE
                              ---------
                              09-APR-09
                              
                              SQL> SELECT to_date(to_char(SYSDATE, 'dd/mm/yyyy HH:MI:SS'),'dd/mm/yyyy HH:MI:SS') FROM DUAL;
                              
                              TO_DATE(T
                              ---------
                              09-APR-09
                              
                              SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
                              
                              Session altered.
                              
                              SQL> select sysdate from dual;
                              
                              SYSDATE
                              -------------------
                              09/04/2009 09:51:42
                              
                              SQL> SELECT to_date(to_char(SYSDATE, 'dd/mm/yyyy HH:MI:SS'),'dd/mm/yyyy HH:MI:SS') FROM DUAL;
                              
                              TO_DATE(TO_CHAR(SYS
                              -------------------
                              09/04/2009 09:51:53
                              
                              SQL>
                              • 12. Re: ORA-01843 : NOT A VALID MONTH ERROR
                                Kamran Agayev A.
                                If OP's NLS_DATE_FORMAT value is DD-MON-RRRR and he cann't change it in SESSION level, then he should use conversion by identifying format
                                If he can change NLS_DATE_FORMAT paramaeter in SESSION level, then he should use sysdate without any convertion

                                - - - - - - - - - - - - - - - - - - - - -
                                Kamran Agayev A. (10g OCP)
                                http://kamranagayev.wordpress.com
                                • 13. Re: ORA-01843 : NOT A VALID MONTH ERROR
                                  6363
                                  he should use conversion by identifying format
                                  There is no need to convert anything, a date is being turned into a formatted string and then being converted back to the same date again. Why would anyone do this?
                                  • 14. Re: ORA-01843 : NOT A VALID MONTH ERROR
                                    SanjayRs
                                    select systimestamp from dual;
                                    Ss