2

In a oracle table column, there is a varchar2 column which contains value '2017-02-14T18:04:34.988+00:00' I need to convert it to timestamp. I am using TO_TIMESTAMP() to convert but I am not able to find out what date mask I need to provide. I have tried with

sql> select to_timestamp('2017-02-14T18:04:34.988+00:00','YYYY-MM-DD HH24:MI:SS.FF') from dual ;

But it does not work.

Kindly suggest what datetime mask I have to specify as 2nd argument.

2

According to ISO 8601 T is the time designator. You need to use a constant "T" in your date format mask.

And your time stamp contains timezone so you need to use TO_TIMESTAMP_TZ function to achieve this.

SQL> select to_timestamp_tz('2017-02-14T18:04:34.988+00:00',' YYYY-MM-DD"T"HH24:MI:SS.FF TZH:TZM') from dual;

TO_TIMESTAMP_TZ('2017-02-14T18:04:34.988+00:00','YYYY-MM-DD"T"HH24:MI:SS.FF
---------------------------------------------------------------------------
14-FEB-17 06.04.34.988000000 PM +00:00

Documentation:TO_TIMESTAMP_TZ

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.