Search code examples
sqloracle-databasedatetimetype-conversiontimestamp-with-timezone

(Oracle) convert date string to datetime

How to convert a date string with the following format into datetime when running a select statement?

2015-06-04T02:58:00.134+08:00

It is stored in a column with data type VARCHAR2(100 BYTE).

Thanks!

Solution

  • It is stored in a column with data type VARCHAR2(100 BYTE).

    First of all, you should never ever store DATE/TIMSTAMP as string. It is a database design flaw.

    Anyway, you could convert it to TIMESTAMP WITH TIMEZONE.

    For example,

    SQL> SELECT to_timestamp_tz('2015-06-04T02:58:00.134+08:00',
  2                         'YYYY-MM-DD"T"HH24:MI:SS.FF TZH:TZM')
  3                         AT TIME ZONE '+8:00' as tm_stamp
  4  FROM dual;

TM_STAMP
-----------------------------------------------------------------
04-JUN-15 02.58.00.134000000 AM +08:00

SQL>