Take the 2-minute tour ×

I have string of date from xml file of such kind: '2010-09-09T22:33:44.OZ'

I need to extract only date and time. I want to ignore symbol T and .OZ (time zone). Which mask I should use? Thanks in advance

share|improve this question
possible duplicate of Converting a XSD date xs:dateTime to an Oracle Date –  APC Sep 16 '10 at 9:20

2 Answers 2

up vote 9 down vote accepted
select TO_DATE('2010-09-09T22:33:44.OZ'
from dual;

9/09/2010 10:33:44 PM
share|improve this answer

If the timezone information is needed:

select to_timestamp_tz('2010-09-09T22:33:44.GMT','YYYY-MM-DD"T"HH24:MI:SS.TZR')
from dual;

09-SEP-10 GMT

But OZ isn't a recognised timezone abbreviation, so you'd need to do some pre-conversion of that to something that is.

If you want to just ignore that part, and it's fixed, you can do what @Jeffrey Kemp said:

select to_date('2010-09-09T22:33:44.OZ','YYYY-MM-DD"T"HH24:MI:SS."OZ"')
from dual;

09/09/2010 22:33:44 -- assuming your NLS_DATE_FORMAT is DD/MM/YYYY HH24:MI:SS

If you want to ignore it but it isn't fixed then you'll need to trim it off first, something like (using a bind variable here for brevity):

var input varchar2(32);
exec :input := '2010-09-09T22:33:44.OZ';
select to_date(substr(:input,1,instr(:input,'.') - 1),'YYYY-MM-DD"T"HH24:MI:SS')
from dual;

09/09/2010 22:33:44
share|improve this answer
OZ stands for Ortszeit, which is German for Local Time. –  APC Sep 16 '10 at 9:24
Oh, OK, so I guess that means the timezone part can be ignored. In which case @Jeffrey's answer is rather more succinct. –  Alex Poole Sep 16 '10 at 9:36
thank you for your answer –  Andrey Khataev Sep 16 '10 at 20:36
Thanks APC, I initially thought OZ was some kind of unofficial Australian timezone :) –  Jeffrey Kemp Sep 17 '10 at 2:39
Wasn't just me then... –  Alex Poole Sep 17 '10 at 7:40

Your Answer


