Oracle - Convert string to timestamp

Question

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

MT0 · Accepted Answer · 2021-10-22 09:13:59Z

3

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

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;

edited Oct 22, 2021 at 9:13

answered Oct 22, 2021 at 8:28

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bullfighter Over a year ago

Great answer, did the trick! Gotta deal with an ORA-01830 "Oracle date format picture ends before converting entire input string" error now though. Do I need to specify NLS somewhere as well?

bullfighter Over a year ago

That is, when I switch the timestamp string with the column I try to convert

Collectives™ on Stack Overflow

Oracle - Convert string to timestamp

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related