Oracle SQL how to convert time zone string to date

Question

I have following 2015-06-17T00:00:00.000+05:00 string. I want to convert this string to Date using oracle sql. I tried lot of format mask but none works for me :

SELECT TO_DATE('2015-06-17T00:00:00.000+05:00','yyyy-mm-dd HH24:MI:SS TZR') FROM DUAL;

Any idea which format mask should i apply for above conversion. Also please note that i only need date information i.e (mm-dd-yyyy). So its also ok if the conversion results in date information only (i.e skipping time information)

Answer 1

This should work:

SELECT TO_DATE(SUBSTR('2015-06-17T00:00:00.000+05:00',1,10),'yyyy-mm-dd') from dual

Answer 2

If you need to keep track of the time zone you should probably look at something like this:

SELECT CAST(TO_TIMESTAMP_TZ('2015-06-17T00:00:00.000+05:00','yyyy-mm-dd"T"HH24:MI:SS.FFTZH:TZM') AT TIME ZONE 'UTC' AS DATE) FROM DUAL;