Sai Sai - 1 year ago 176
SQL Question

Trying to load timezone data using sql loader

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

Ex:Data format is


In the control file I'm using the TO_TIMESTAMP_TZ


Answer Source

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'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download