Oracle: String to Date, Timestamp with Timezone

I recently got a text from a friend to convert '2021-04-07T14:03:54.000000-0700' string to date.

He Tried -

SQL> select
  2  to_date('2021-04-07T14:03:54.000000-0700','YYYY-MM-DDHH:MI:SS.FFTZH-TZM')
  3  from dual;
to_date('2021-04-07T14:03:54.000000-0700','YYYY-MM-DDHH:MI:SS.FFTZH-TZM')
                                          *
ERROR at line 2:
ORA-01821: date format not recognized

but it was failing with ORA-01821: date format not recognized.

There are 2 issues in above SQL. 

1. The format in the SQL is itself incorrect. The correct format is 'YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM'.

SQL> select
  2  to_date('2021-04-07T14:03:54.000000-0700','YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM')
  3  from dual;
to_date('2021-04-07T14:03:54.000000-0700','YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM')
                                          *
ERROR at line 2:
ORA-01821: date format not recognized

2. The use of the TO_DATE function is incorrect also, the value is timestamp with timezone, so we can try to_timestamp instead of to_date

SQL> select
  2  to_timestamp('2021-04-07T14:03:54.000000-0700','YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM')
  3  from dual;
to_timestamp('2021-04-07T14:03:54.000000-0700','YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM')
             *
ERROR at line 2:
ORA-01821: date format not recognized

but to_timestamp is also failing. 

Oracle provides to_timestamp_tz to convert a string to timestamp with timezone.

SQL> select
  2  to_timestamp_tz('2021-04-07T14:03:54.000000-0700','YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM')
  3  from dual;

TO_TIMESTAMP_TZ('2021-04-07T14:03:54.000000-0700','YYYY-MM-DD"T"HH24:MI:SS.
---------------------------------------------------------------------------
07-APR-21 02.03.54.000000000 PM -07:00

Now we just need to convert this to date.

SQL> select
  2  cast(
  3  to_timestamp_tz('2021-04-07T14:03:54.000000-0700',
  4  'YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM'
  5  ) as date
  6  )
  7  from dual;

CAST(TO_TIMESTAMP_TZ
--------------------
07-Apr-2021 14:03:54

If someone do not want to go into these details, he can simply remove the data after seconds and then convert it to date

SQL> select
  2  to_date(
  3  regexp_replace('2021-04-07T14:03:54.000000-0700','\..+'),
  4  'YYYY-MM-DD"T"HH24:MI:SS'
  5  )
  6  from dual;

TO_DATE(REGEXP_REPLA
--------------------
07-Apr-2021 14:03:54

Related Posts - 

- Oracle: Some Important Date Queries 

- Playing With Truncate and Date 

- Prefer Oracle Native Date Arithmetic over ANSI INTERVAL 

- ORA-01830 date format picture ends before converting entire input string