Formatting Dates passed from EBS to XML Publisher Templates

I often get asked by Template Designers how to change the format mask of the date values passed from EBS data templates.

It seems pretty obvious until you discoverer that the seeded Value set for concurrent program date validation 'FND_STANDARD_DATE' returns a date in the XML file with the format YYYY-MM-DD hh:mm:ss+HH:MM e.g 2008-06-09 00:00:00.0

XML Publisher is expecting the date to be in the format: YYYY-MM-DDThh:mm:ss+HH:MM e.g 2008-06-09T00:00:00.0

Notice, that pesky 'T' separator between the date and time component in the second date!!!

A post from Tim back in 2006 nicely explains this issue: blogs.oracle.com/xmlpublisher/2006/11/03


He recommends using the following sql syntax for coding dates directly in your data template:

to_char(TRX_DATE, 'YYYY-MM-DD"T"hh:mm:ss+HH:MM')

where TRX_DATE is the date field being selected


This works well if you can change the data template. However, there are many cases in EBS where the data templates cannot be viewed or modified.


One solution that I have found is to add the 'T' seperator to the date field in the Template e.g.

Create a new form field in your template using the code:

?xdofx:replace(TRX_DATE,' ','T')?

where TRX_DATE is the tag name of the date field.

You can then happily format the date in your template using a standard Microsoft format mask!

Anyone got another solution?