Oracle BI Publisher Blog

Date formatting in XML Publisher is very powerful, it can provide a variety of formats from simple 03/12/99 to 'Friday, December 31, 1999 6:15 PM GMT' ... notice the timestamp and the timezone components. All very neat but how do you get there?

Get the right format ...

Well the first hurdle is to get your dates in the XSD date-time format:
                  YYYY-MM-DDThh:mm:ss+HH:MM
Looks straightforward and you ought to be able to get the database 'to_char' function to serve it up no problem, but there is a wrinkle in there in the form of that 'T' character its used as a separator between date and time components. Try and use that format mask with the to_char and the database will choke ... the format is not recognised. There is hope, even for Oracle Reports under EBS users :)

1. If you are not interested in the time and zone info then just use the 'YYYY-MM-DD' format mask, no need for the 'T'. Oracle Reports and the db support this mask. XMLP will then format the date appropriately. any timestamp is going to be 12:00:00 AM and the timezone will default to GMT
2. Use the XMLP extraction engine - the engine will extract all dates using the XSD mask - simple
3. If you're using Oracle Reports or your own plsql/sql extraction routine and want the timestamp and zone then only way I have found to construct the mask is to use something like:

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

this will at least get the date into the right format.

I stand corrected on this thanks to an anonymous comment, I was playing with masks in the 
db while writing and could not get the format to work, however our anonymous friend has:
to_char(sysdate, 'YYYY-MM-DD"T"hh:mm:ss+HH:MM') which works perfectly ... apologies!

Date formatting and calculations

Now you have date in the right format you can apply masks in the layout very easily. You can either use the MSWord formats or XMLP provides a format-date function ... there is a complete section devoted to the formatting in the user guide. Page 118 is the start and covers all XMLP flavors.
The other advantage of the mask is that calculations on dates become possible with XSLT 2.0, prior to this all dates were treated as strings not much use when it comes to calculating the number of days between two dates. Thats another article in itself ... there are plenty of resources out there now.