Search code examples
oracledatetimeoracle11gsql-loader

Oracle sqlldr timestamp format issue

I'm having an issue getting sqlldr to import a DAT file data file into my table, specifically with the field that is a timestamp.

2018-11-02T20:54:38.000000+0000

I've tried all manner of combinations in my control file and am going around in circles. Does anyone know what should be using in my control file for this above timestamp format?

For reference, this is what I've most recently tried:

load data
infile 'feed.dat'
into table cust_acct
fields terminated by "|" 
( ...
  updateddatetime TIMESTAMP "YYYY-MM-DD-HH24.MI.SS", 
...)
Solution

  • The date 2018-11-02T20:54:38.000000+0000 has a time-zone component so you want TIMESTAMP WITH TIME ZONE data type and you have 6 fractional seconds digits so your data type should have precision of 6.

    In a DateTime format model you can use double quotes to indicate a literal string and, in sqlldr you can escape the double quotes with a backslash:

    updateddatetime TIMESTAMP(6) WITH TIME ZONE "YYYY-MM-DD\"T\"HH24:MI:SS.FF6TZR",

    or

    updateddatetime TIMESTAMP(6) WITH TIME ZONE "YYYY-MM-DD\"T\"HH24:MI:SS.FF6TZHTZM",