Using toChar to output a date in W3C XML Schema xs:dateTime type format

oracle, oracle11g, plsql, sql

Solution

Try:

 select to_char(sysdate, 'yyyy-mm-dd') || 'T' || to_char(sysdate,'hh24:mi:ss') || sessiontimezone
from dual;

Returns: 2013-08-16T13:00:51+00:00

Problem

I'm trying to convert `sysdate` using `toChar` to the following format: `2006-11-20T17:10:02+01:00` From this format: `16/08/2012 13:40:59` Is there a standard way of doing this? I've tried using the `toChar` to specific the `T` part as a string but it doesn't appear to be working. Thanks in advance Jezzipin EDIT: I've tried Nicholas' solution however as I mention above, I need to use sysdate. I've used the following select query: ``` select to_char(to_timestamp_tz(sysdate-365, 'dd/mm/yyyy hh24:mi:ss'),'yyyy-mm-dd"T"hh24:mi:ss TZH:TZM') from dual; ``` However, this returns: ``` 0012-08-16T00:00:00 +01:00 ``` which is incorrect as it should be 2012-08-16T00:00:00 +01:00

Original source