9 Replies Latest reply: Dec 11, 2012 4:15 AM by 976439 RSS

    Import Date and Time Values

    976439
      Hi, i am looking for some help in regards to import data into a new table. I have 3 columns with date and times and when importing I am changing the data type as DATE with the format specified as DD/MON/RR HH24:MI:SS (options in preference for NLS are set the same) and the size set at 255. This verifies all ok and When I finish this than I get the following error:

      insert failed for row 1 time GDK-05043 not a valid month

      I then changed the datatype to TIMESTAMP DD-with the format MON-RR HH24.MI.SSXFF but this still gives me the same error.

      When I import with the datatype set as VARCHAR2 then this imports all the data.

      An example of a date and time value from the csv is the following:

      01/12/2012 22:51:57

      If somebody could please help me fix this as ideally want these as date and time values in their tables. Thanks in advance.
        • 1. Re: Import Date and Time Values
          Hoek
          What is it exactly you need to do?
          Normallly TO_DATE should do the trick, unless you have junk/garbage data in your csv...
          SQL> select '01/12/2012 22:51:57' my_string
            2  ,      to_date('01/12/2012 22:51:57', 'dd/mm/yyyy hh24:mi:ss') my_valid_date
            3  from   dual;
          
          MY_STRING           MY_VALID_DATE
          ------------------- -------------------
          01/12/2012 22:51:57 01-12-2012 22:51:57
          
          1 row selected.
          And stop using 2 digits for the year part, use 4 digits!
          • 2. Re: Import Date and Time Values
            ranit B
            01/12/2012 22:51:57
            try this...
            select to_char(to_date('01/12/2012  22:51:57','dd-mm-yyyy hh24:mi:ss'),
                            'dd/Mon/rr hh24:mi:ss') from dual;
            gives
            01/Dec/12 22:51:57
            In CSV, the data will be in String type. So, first convert it to Date type and then try changing it's format.
            Afaik, TO_CHAR is used only to mould the output Date into different Formats.

            Hoek,
            Please rectify me if i'm wrong.

            Ranit B.

            Edited by: ranit B on Dec 10, 2012 8:08 PM
            • 3. Re: Import Date and Time Values
              BluShadow
              973436 wrote:
              An example of a date and time value from the csv is the following:

              01/12/2012 22:51:57
              So... do you think that that data in your CSV matches the format you've specified:
              DD/MON/RR HH24:MI:SS
              DD - looks ok
              MON - hmmm, looks more like it should be MM
              RR - seriously.... the year has got 4 digits, so why are you using RR? Use YYYY
              HH24 - looks ok
              MI - looks ok
              SS - looks ok.
              • 4. Re: Import Date and Time Values
                AlbertoFaenza
                Hi,

                how are you importing your data in cvs? SQL*Loader or external table?

                If you are using SQL*Loader check here below:

                Input data testdate.dat:
                1,01/12/2012 22:51:57
                2,02/12/2012 10:24:30
                3,03/12/2012 11:33:22
                4,04/12/2012 15:14:16
                Control file:
                --testdate.ctl
                load data
                INFILE 'testdate.dat'
                APPEND
                INTO TABLE testdate
                FIELDS TERMINATED BY ','
                TRAILING NULLCOLS
                (
                   id      
                 , dt      DATE 'dd/mm/yyyy hh24:mi:ss'
                )
                Table creation:
                CREATE TABLE testdate
                (
                   id   NUMBER
                 , dt   DATE
                );
                Input with SQL*Loader:
                sqlldr username/password@yourdb control=testdate.ctl
                SQL*Loader: Release 11.2.0.1.0 - Production on Mon Dec 10 15:40:47 2012
                
                Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
                
                Commit point reached - logical record count 4
                Data in database:
                SELECT id, TO_CHAR(dt, 'DD/MM/YYYY HH24:MI:SS') dt 
                  FROM testdate;
                
                        ID DT                 
                ---------- -------------------
                         1 01/12/2012 22:51:57
                         2 02/12/2012 10:24:30
                         3 03/12/2012 11:33:22
                         4 04/12/2012 15:14:16
                Regards.
                Al
                • 5. Re: Import Date and Time Values
                  BluShadow
                  Example:
                  SQL> select to_date('01/12/2012 22:51:57','DD/MON/RR HH24:MI:SS') from dual;
                  select to_date('01/12/2012 22:51:57','DD/MON/RR HH24:MI:SS') from dual
                                 *
                  ERROR at line 1:
                  ORA-01843: not a valid month
                  
                  
                  SQL> select to_date('01/12/2012 22:51:57','DD/MM/YYYY HH24:MI:SS') from dual;
                  
                  TO_DATE('01/12/20122
                  --------------------
                  01-DEC-2012 22:51:57
                  ... now how difficult was that to test?
                  • 6. Re: Import Date and Time Values
                    976439
                    Thanks guys for all your inputs, the reason i was putting RR was another thread I had read this morning which specified to check Preferences > Database > NLS and check Date Format. All I wanted was for the data to be imported from csv without me importing the date and time fields as text and then running queries on top to fix this issue.

                    When importing and setting the format for date as 'DD/MM/YYYY HH24:MI:SS' is now working and all data is coming through as required.

                    Wanted to thank everyone for their input and advise really appreciated.

                    Btw unfortunately I dont have SQL loader so cant load any scripts to automate this, but any links on how i can install sqlloader would be really helpful for my development.

                    Thanks once again
                    • 7. Re: Import Date and Time Values
                      Hoek
                      Instead of SQL*Loader, I would personnally encourage you to use an External Table.
                      Read about it (amongst others) here:
                      http://www.oracle-base.com/articles/9i/external-tables-9i.php
                      http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6611962171229
                      http://www.oracle-developer.net/display.php?id=204
                      http://www.oracle-developer.net/display.php?id=512
                      • 8. Re: Import Date and Time Values
                        AlbertoFaenza
                        973436 wrote:
                        Thanks guys for all your inputs, the reason i was putting RR was another thread I had read this morning which specified to check Preferences > Database > NLS and check Date Format. All I wanted was for the data to be imported from csv without me importing the date and time fields as text and then running queries on top to fix this issue.

                        When importing and setting the format for date as 'DD/MM/YYYY HH24:MI:SS' is now working and all data is coming through as required.

                        Wanted to thank everyone for their input and advise really appreciated.

                        Btw unfortunately I dont have SQL loader so cant load any scripts to automate this, but any links on how i can install sqlloader would be really helpful for my development.

                        Thanks once again
                        Hi,

                        there is a specific forum for SQL*Loader: {forum:id=732}

                        SQL*Loader is part of Oracle client and can be installed as part of the client installation. The binary is sqlldr (in Unix) or sqlldr.exe in Windows.
                        It's still not clear to me how you currently import your CSV data.

                        An additional remark. If you are satisfied with the answer please mark your question as answered.

                        Regards.
                        Al
                        • 9. Re: Import Date and Time Values
                          976439
                          Alberto, thanks for your response. Currently I am able to import the csv file by selecting import data on the required table, selecting csv file, and then I am going through the data import wizard steps. Also I make sure than within the column definition step i put the format DD/MM/YYYY HH24:MI:SS for all data fields and this imports the required data into the table.

                          In regards to SQL Loader (never used this before) i tried to run sqlldr from a sql worksheet in sql developer but this didnt work. I am not too sure if this is the correct method to run this but thats all i tried as cant find much documentation in how to get this started.

                          Thanks for the link as this will help me try and understand how this all works.

                          Appreciate your time and support.