Oracle - Convert string to timestamp-CodePudding

I have this timestamp string:

'2021-09-07T18:24:25.075 02:00'

which should be converted to the following format:

'dd.mm.yyyy hh.mi.ss'

Got this so far, but the format seems to be unrecognizable by Oracle. Any ideas?

select to_char(to_timestamp('2021-09-07T18:24:25.075 02:00', 'yyyy-mm-ddThh:mi:ss'), 'dd.mm.yyyy hh.mi.ss') from dual

CodePudding user response：

Use double quotes around the literal characters inside the format model and use TO_TIMESTAMP_TZ with the TZH:TZM format models to match the time stamp (and use FF to match the fractional seconds and HH24 for a 24 hour clock):

select to_char(
         to_timestamp_tz(
           '2021-09-07T18:24:25.075 02:00',
           'yyyy-mm-dd"T"hh24:mi:ss.ffTZH:TZM'
         ),
         'dd.mm.yyyy hh24.mi.ss'
       ) AS timestamp
from   dual

Outputs:

TIMESTAMP

07.09.2021 18.24.25

TIMESTAMP
07.09.2021 18.24.25

db<>fiddle here

You asked in comments:

Gotta deal with an ORA-01830 "Oracle date format picture ends before converting entire input string" error now though. ... That is, when I switch the timestamp string with the column I try to convert

From Oracle 12, you can use:

select to_char(
         to_timestamp_tz(
           column_name
           DEFAULT NULL ON CONVERSION ERROR,
           'yyyy-mm-dd"T"hh24:mi:ss.ffTZH:TZM'
         ),
         'dd.mm.yyyy hh24.mi.ss'
       ) AS timestamp
from   table_name

Then all the values in the column that do not match your format model will return values of NULL. You can use this for debugging and find the rows with invalid data.

I.e.

select column_name
from   table_name
WHERE  to_timestamp_tz(
         column_name
         DEFAULT NULL ON CONVERSION ERROR,
         'yyyy-mm-dd"T"hh24:mi:ss.ffTZH:TZM'
       ) IS NULL;