3 Replies Latest reply on May 14, 2012 11:47 PM by Srini Chavali-Oracle

    SQL Loader and formatting dates

    937291
      I've got dates formatted like so: 2012-05-10T17:04:51-08:00

      How can I get SQL Loader to load these into a Date column??


      Thanks
        • 1. Re: SQL Loader and formatting dates
          Srini Chavali-Oracle
          Pl post details of OS and database versions. What do the "T" and "-08.00" refer to ?

          You will need to match the time format that Oracle supports

          http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements004.htm#i34924
          http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_field_list.htm#autoId23

          HTH
          Srini
          • 2. Re: SQL Loader and formatting dates
            937291
            Thanks for the reply-

            SQL Loader is running on Win, Oracle 11 on Linux

            The format is standard UTC

            YYYY-MM-DDTHH24:MI:SS±HH:MM Where the ±HH:MM refers to time zone offset from GMT. The "T" is just a separator between date and time (and is always "T").

            I looked at your references, and tried a few dozen variants without success.

            Most of my attempts have been using something similar to this

            "YYYY-MM-DDTHH24:MI:SSTZH:TZM"

            or this:

            "YYYY-MM-DD'T'HH24:MI:SSTZH:TZM"

            or this:

            'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM'

            Thanks
            • 3. Re: SQL Loader and formatting dates
              Srini Chavali-Oracle
              There is no Oracle mask that matches "2012-05-10T17:04:51-08:00" that I am aware of. You will need to modify your data file to match one of the masks that Oracle supports

              HTH
              Srini