CODEWITHSUNDEEP
sqloracle-databasetimestamp
DPEZ
DPEZ

Reputation: 117

Oracle SQL read timestamp from value in format YYYYMMDDThhmmss

I'm trying to run the following query using Oracle SQL

select to_timestamp('2015/06/20T00:00:00','yyyy/mm/dd hh24:mi.ss.ff') from dual;

However this gives me the following error

ORA-01858: a non-numeric character was found where a numeric was expected

If I remove the "T" from the query it runs fine and returns the expected value, such as below.

select to_timestamp('2015/06/20 00:00:00','yyyy/mm/dd hh24:mi.ss.ff') from dual;

My question is, is there a way to convert the value containing the T into a timestamp or will the format always be incorrect?

Please let me know if you require and more information and I shall do my best to provide, thanks for your help.

Upvotes: 3

Views: 611

Answers (2)

Husqvik
Husqvik

Reputation: 5809

Just include the other characters within double quotes:

select to_timestamp('2015/06/20T00:00:00','yyyy/mm/dd"T"hh24:mi.ss.ff') from dual

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You could use replace():

select to_timestamp(replace('2015/06/20T00:00:00', 'T', ' '),
                    'yyyy/mm/dd hh24:mi.ss.ff'))
from dual;

Upvotes: 2

Related Questions