Selecting a Non-Standard Date Format Using Oracle TO_CHAR

The project I'm currently working on requires the passing of a date to a web service in a format that looks like the following:

2013-03-29T04:28:00.000

Since the "T" and the trailing "000" characters are not valid mask characters for the Oracle TO_CHAR function, I had to get a bit tricky.



Here's what I tried first:

select to_char(sysdate,'yyyy-mm-ddThh:mi:ss.000')  from dual;

But, as the "T" and the trailing "000" characters cannot be used as part of the Oracle TO_CHAR mask, I received the following error when trying to run the SQL:

ORA-01821: date format not recognized

The solution was to concatenate multiple TO_CHAR commands into one value returned from the database, together with the "T" and "000" characters. Here using sysdate and dual is the syntax that worked:

select to_char(sysdate,'yyyy-mm-dd') ||'T'|| to_char(sysdate,'hh:mi:ss') || '.000' from dual;