Oracle BI Publisher Blog

Have you ever faced a situation where your dates are just gone wild? For example, you could swear that the date in your XML data is 2009-03-01 (March 1st, 2009), but it shows up on your report as 28-FEB-2009!

I have seen several emails the last few months come to the help mailing list with date formatting related questions, that I thought this might be a worthwhile topic for a blog article.

The cause of this, what some call “odd” behavior, is a combination of four things:

1. How dates are stored in the XML data
2. Whether and how you format dates in the RTF template
3. The timezone that you run the report
4. And this thing called Coordinated Universal Time known as UTC
   

If you find the acronym UTC confusing, it’s because it’s a backronym, not an acronym! Long story short, blame it on the French (or the Americans, depending on which side of the Atlantic you are!) You can read all about it here: http://en.wikipedia.org/wiki/Coordinated_Universal_Time.

So…, here is how these four things together can ruin your “date”!

1. At run time, BI Publisher server converts dates that you have applied a format mask in your RTF template to the timezone that the report is destined for (the report timezone).
2. When you apply a date format mask to a field in your RTF template, BI Publisher expects the incoming dates from the XML data to be in canonical format. That is, YYYY-MM-DDThh:mm:ss+HH:MM, a standard way to represent date and time in reference to UTC. The +HH:MM is the positive or negative difference of the timezone where the data originates from UTC/GMT.
3. If the time component and UTC offset is omitted (i.e. the XML date is in the format YYYY-MM-DD), BI Publisher interprets the date as YYYY-MM-DDT00:00:00-00:00 (i.e. midnight GMT).

If you are responsible for generating the data for the report, you don’t need to worry about converting your dates to canonical format. BI publisher will do that automatically for you.

For example, when you issue the query “Select SYSDATE from DUAL” from BI Publisher, it generates the following XML data set:

<ROWSET>
<ROW>
<SYSDATE>2009-06-03T18:44:32.000-07:00</SYSDATE>
</ROW>
</ROWSET>

Of course no one likes to see dates on their report in that format!

Provided, you want dates and time to be converted to the appropriate time zone the report is destined for, the best and most straightforward approach to address that is to apply a format mask to the date field in your RTF template and let BI Publisher take care of the rest. 

For information on how and the types of format masks that you can apply to a date field refer to Number and Date Formatting in the product documentation: http://download.oracle.com/docs/cd/E12844_01/doc/bip.1013/e12187/T421739T481157.htm#4535403

You can also view the Formatting Dates demo available in our Demonstration Library on our OTN page. The URL to out demonstration library is:
http://www.oracle.com/technology/products/xml-publisher/demoshelf/shelf.html

Keeping in mind that if you apply a format mask to a date field in your RTF template, it will result in the conversion of dates and times depending on the report timezone, it’s quite normal for a date to roll back or forward by 1 when your run the report!

EXAMPLE 1
If you use <?format-date:SYSDATE;'LONG_TIME'?> in your RTF template, with the following data set:

<ROWSET>
<ROW>
<SYSDATE>2009-06-03T18:44:32.000-07:00</SYSDATE>
</ROW>
</ROWSET>

It will result in these dates displayed on the report:
------------------------------------------------------------------------------------------------------------------------
Report Timezone                                                                             SYSDATE
------------------------------------------------------------------------------------------------------------------------
[GMT – 07:00] – America/Los _Angeles                                  Wednesday, June 3, 2009 6:44 PM
[GMT + 08:00] – Asia/Singapore                                              Thursday, June 4, 2009 9:44 AM
------------------------------------------------------------------------------------------------------------------------

Think about it, in this example, the value of SYSDATE is the time I am writing this blog article in California, Wednesday 18:44:32 (6:44 PM) PDT on June 3rd. My friend Babar in Singapore, 15 hrs ahead of me, is having his third cup of coffee at 9:44 AM, Thursday morning of Jun 4th! Nothing “odd” about that!

EXAMPLE 2
If instead the XML date in the data is in the format YYYY-MM-DD, as in the following data set,

<ROWSET>
<ROW>
<SYSDATE>2009-06-03</SYSDATE>
</ROW
</ROWSET>

Running the report using the same RTF template as above, it will result in these dates displayed on the report:
-----------------------------------------------------------------------------------------------------------------------
Report Timezone                                                                             SYSDATE
------------------------------------------------------------------------------------------------------------------------
[GMT – 07:00] – America/Los _Angeles                                  Tuesday, June 2, 2009 5:00 PM
[GMT + 08:00] – Asia/Singapore                                             Wednesday, June 3, 2009 8:00 AM
------------------------------------------------------------------------------------------------------------------------

The same reasoning applies here. BI Publisher interprets the value of SYSDATE to represent Wednesday, June 3, 12 AM GMT. For me, 7 hrs behind during daylight savings time, it’s still June 2, 5:00 PM. And my friend Babar in Singapore has just finished his first cup of coffee on June 3rd!

What if you don’t want dates to be converted based on the report timezone?

One solution to that is to extract dates in the format that you want them to appear on your report and don’t apply a format mask to date fields in the RTF template.  Chances are that BI Publisher will ignore the format mask anyway, unless the format that you extract the dates in matches the date part of the canonical format that dates are expected to be in (i.e., YYYY-MM-DD).

To extract dates in a specific format, you can use a TO_CHAR function in the query.  For example, "Select TO_CHAR(SYSDATE,’DD/MM/YYYY’) as SYS_DATE from DUAL" will generate the following data set:

<ROWSET>
<ROW>
<SYS_DATE>03/06/2009</SYS_DATE>
</ROW>
</ROWSET>

The other option is to extract dates in the format required by BI Publisher, and specify a timezone in the format mask for the conversion to override the default, which is the report timezone. If you specify a timezone with the same UTC offset as the dates in the XML data, no conversion takes place.

For example, <?format-date:SYSDATE;'LONG';’America/Los_Angeles’?> with the data in EXAMPLE 1, will always result in Wednesday, June 3, 2009 displayed on the report.

On the other hand, <?format-date:SYSDATE;'LONG';’UTC’?> with the data in EXAMPLE 2, will also result in Wednesday, June 3, 2009 displayed on the report.

Conclusion –
If you’d like your “dates” to behave, be nice to them and play by the rules!