Oracle (SQL Developer): How to convert TIMESTAMP string includeing UTC Offset

1
votes

Objective

Identify the correct TIMESTAMP format and the cause of the issue.

Problem

Tring to load a CSV which includes timestamp including UTC offset. 

2014-01-01T00:38:51.000+11:00

The format string is below.

YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM

However, getting an error message.

Invalid format YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM is specified.

SQL Developer

TIMESTAMP

enter image description here

TIMESTAMP with TZ enter image description here

Please suggest how to fix this and the reason.

References

Oracle 9: Convert date from mm/dd/yyyy hh:mm:ss format to iso8601 formatted datetime [closed]

1
sqloracletimestamporacle-sqldeveloperutc
The data type you want is TIMESTAMP WITH TIMEZONE, not TIMESTAMP. Best of luck. - Bob Jarvis - Reinstate Monica
@Bob Jarvis, thanks for the comment. However it still gave the same error. - mon

1 Answers

2
votes

There is nothing wrong with the timestamp with timezone format:

SQL> select to_timestamp_tz('2014-01-01T00:38:51.000+11:00',
  2       'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM') as result from dual;

RESULT
---------------------------------------------------------------------------
01-JAN-14 12.38.51.000000000 AM +11:00

1 row selected.

Elapsed: 00:00:00.00

(What is DISPLAYED is in a different format - it uses my NLS_TIMESTAMP_TZ_FORMAT session parameter - but the conversion from string to timestamp with timezone worked perfectly fine.)

Definitely a SQL Developer issue - you will need to find out how this is done in their interface.