8 Replies Latest reply on Feb 5, 2018 9:35 PM by mathguy

    ORA-01843: not a valid month

    hartford27

      Hi, I am trying to convert a datetime field to date format i.e. YYYY\MM\DD, I am running into the ORA - 01843 error with this

       

      TO_DATE(Table__1.Start_Time, 'DD-MON-YYYY'). The data for Table__1.Start_Time is:

       

      Wednesday March 01 2017

      Wednesday March 03 2017

      Thursday March 01 2017

       

      Please help me with this issue.

        • 1. Re: ORA-01843: not a valid month
          Frank Kulash

          Hi,

          hartford27 wrote:

           

          Hi, I am trying to convert a datetime field to date format i.e. YYYY\MM\DD, I am running into the ORA - 01843 error with this

           

          TO_DATE(Table__1.Start_Time, 'DD-MON-YYYY'). The data for Table__1.Start_Time is:

           

          Wednesday March 01 2017

          Wednesday March 03 2017

          Thursday March 01 2017

           

          Please help me with this issue.

          Your data isn't in 'DD-MON-YYYY' format.  From what you posted, it seems to be in 'Day Month DD, YYYY' format, so use:

          TO_DATE (table__1.start_time, 'Day Month DD, YYYY')

          You can expect lots of problems when you store date information in string columns, like VARCHAR2.  Always use DATE columns for date information.

           

          I hope this answers your question.

          If not, post a little sample data (CREATE TABLE and INSERT statements), and the exqct results you want from that sample data.

           

          EDIT: I made a typo above.  There is no comma after the day in the input, so there shouldn't be a comma after 'DD' in the format string.  See reply #7, below.

          • 2. Re: ORA-01843: not a valid month
            EdStevens

            hartford27 wrote:

             

            Hi, I am trying to convert a datetime field to date format i.e. YYYY\MM\DD, I am running into the ORA - 01843 error with this

             

            TO_DATE(Table__1.Start_Time, 'DD-MON-YYYY'). The data for Table__1.Start_Time is:

             

            Wednesday March 01 2017

            Wednesday March 03 2017

            Thursday March 01 2017

             

            Please help me with this issue.

            What is the data type of Table__1.Start_Time?

            If it is a DATE, why are you trying to pass it to TO_DATE?

            If it is not DATE, then you have A Design Fail of the first order.  The "format" you present the dates in strongly suggest this design fail.

             

             

            Show us your actual query, not some snippet of it.

            Show us the output of 'DESC TABLE__1'

            Show us the output of 'SELECT START_TIME FROM TABLE__1'.

            Do all of the above in sqlplus, and use copy and paste to present the results.

             

            Do not confuse storing data in a DATE column vs displaying a date to a user.

            Read Understanding Oracle DATE formats :: edstevensdba

            • 3. Re: ORA-01843: not a valid month
              Frank Kulash

              Hi,

               

              When you have a question, always post your database version.

              The VALIDATE_CONVERSION function might help in the problem, but it was a new feature in Oracle 12.2.

               

              See the Forum FAQ: Re: 2. How do I ask a question on the forums?

              • 4. Re: ORA-01843: not a valid month
                EdStevens

                Frank's comment jogged me to realize that perhaps you are trying to correct a design fail by converting an existing varchar to a DATE.  If that's the case (or even if not) you need to provide more detail and context.

                 

                But if you ARE trying to clean up a design fail, take a look at your data and compare that to the mask you provide for TO_DATE:

                 

                TO_DATE(Table__1.Start_Time, 'DD-MON-YYYY'). The data for Table__1.Start_Time is:

                 

                Wednesday March 01 2017

                Wednesday March 03 2017

                Thursday March 01 2017

                Your format mask is 'dd-mon-yyyy'.  Does that accurately describe 'Wednesday March 01 2017'?

                Or is this a case of most of the rows are in 'dd-mon-yyyy' but then you have some rows as above?  Again, your question lacks any context by which we are to evaluate the real problem.

                • 5. Re: ORA-01843: not a valid month
                  hartford27

                  The data for Table__1.Start_Time is in this format only.

                   

                  Wednesday March 01 2017

                  Wednesday March 03 2017

                  Thursday March 01 2017

                  • 6. Re: ORA-01843: not a valid month
                    mathguy

                    hartford27 wrote:

                     

                     

                    The data for Table__1.Start_Time is in this format only.

                     

                    Wednesday March 01 2017

                    Wednesday March 03 2017

                    Thursday March 01 2017

                     

                    "Format" is not the proper concept; the most basic question is, what is the data type of your column? You won't know that by querying the data (select start_time from table___1). If the data is in data type VARCHAR2, then you will see the actual strings stored in the table. If the data is in data type DATE, you will NOT see what is stored in the table, you will see a string representation (using your NLS_DATE_FORMAT setting). You simply can't tell the difference by just looking at the output of a SELECT query.

                     

                    So, if you can't tell the data type by querying the data, how can you know? ANSWER:  run the command DESCRIBE TABLE___1   (no need for semicolon, since this is NOT a SQL command; it is a SQL*Plus command, which you can also run in Toad, SQL Developer etc.) It will show you the names of the columns in your table, and - most important for the question at hand - their data type. Alternatively, in SQL Developer or Toad you can use the GUI - select the table and look at the COLUMNS tab (or similar); you will see the same information as you could get with the DESCRIBE command.

                     

                    Please run this command or check the GUI info and report back (here) what you found.

                     

                    Then: When you say "I am trying to convert" - what do you mean by that? Are you looking to store the result of the conversion? Or just use it in a SELECT query (and perhaps use the output in a report)? HOW you will convert (if "convert" is even the right concept - depending on the actual data types) will depend on what you need the conversion result for.

                    • 7. Re: ORA-01843: not a valid month
                      Frank Kulash

                      Hi,

                      hartford27 wrote:

                       

                      The data for Table__1.Start_Time is in this format only.

                       

                      Wednesday March 01 2017

                      Wednesday March 03 2017

                      Thursday March 01 2017

                      You really need to post CREATE TABLE and INSERT statements for your sample data.

                       

                      I made a mistake in reply #1.  If there are no commas in the input, then there shouldn't be commmas in the format mask, either.  I should have suggested

                      TO_DATE (table__1.start_time, 'Day Month DD, YYYY')

                      However, if the data is really what you posted, then the TO_DATE call, then you wouldn't get the error you posted.  You'd get a different error: "ORA-01858: a non-numeric character was found where a numeric was expected".  You really need to post a complete test script so the people who want to help you can re-create the problem and test their ideas.

                      • 8. Re: ORA-01843: not a valid month
                        mathguy

                        Further to my comment above, here is an illustration:

                         

                        SQL> create table tbl ( str varchar2(40), dt date );

                         

                        Table created.

                         

                        SQL> insert into tbl values ('Monday February 5 2018', date '2018-02-05');

                         

                        1 row created.

                         

                        SQL> alter session set nls_date_format = 'fmDay Month dd yyyy';

                         

                        Session altered.

                         

                        SQL> select str, dt from tbl;

                         

                        STR                                      DT
                        ---------------------------------------- ---------------------------
                        Monday February 5 2018                   Monday February 5 2018

                         

                        1 row selected.

                         

                        Can you, by just looking at the output, tell that STR is in VARCHAR2 data type, but DT is in DATE data type? I can't.

                         

                        On the other hand:

                         

                        SQL> describe tbl

                         

                        Name                                                              Null?    Type

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

                        STR                                                                        VARCHAR2(40)

                        DT                                                                         DATE