CODEWITHSUNDEEP
sqloracle-databasesql-loader
Sai
Sai

Reputation: 67

Trying to load timezone data using sql loader

Is there a way to load timezone data using sql loader?

Ex:Data format is 2016-04-28T08:42:24.412822Z

In the control file I'm using the TO_TIMESTAMP_TZ

T_DATE "TO_TIMESTAMP_TZ(:T_DATE,'YYYY/MM/DD HH12:MI:SS:FF TZHTZM')"

Upvotes: 0

Views: 1899

Answers (1)

Alex Poole
Alex Poole

Reputation: 191285

Your date format model doesn't match your sample date. I doubt you really want HH12 since there is no AM/PM marker, but the problem is the fixed T from the ISO format. You can handle that as a character literal:

Punctuation and Character Literals in Datetime Format Models
You can include these characters in a date format model:

  • Punctuation such as hyphens, slashes, commas, periods, and colons
  • Character literals, enclosed in double quotation marks

As your SQL*Loader line is already in double-quotes you need to escape that:

t_date "to_timestamp_tz(:T_DATE, 'yy-mm-dd\"T\"hh24:mi:ss.ff tzhtzm')"

Or more simply use the built-in date/time handling instead of an explicit SQL operator, i.e.:

t_date timestamp with time zone 'yyyy-mm-dd"T"hh24:mi:ss.ff tzhtzm'

Upvotes: 2

Related Questions