Geek Answers Handbook

Oracle function to_date. Mask needed

I have a date string from an xml file of this type: '2010-09-09T22: 33: 44.OZ'

I need to extract only the date and time. I want to ignore the T character and .OZ (time zone). What mask should i use? thanks in advance

+6
oracle format to-date mask
source share
2 answers
select TO_DATE('2010-09-09T22:33:44.OZ' ,'YYYY-MM-DD"T"HH24:MI:SS".OZ"') from dual; 9/09/2010 10:33:44 PM
+11
source share

If time zone information is required:

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

But OZ not a recognized time zone reduction, so you need to do some preliminary conversion of what is.

If you just want to ignore this part and it is 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 is not fixed, you first need to trim it, something like (using the binding variable here for short):

 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
+6
source share

More articles:


All Articles