Reputation: 117
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
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
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