How to keep your “dates” from going wild!

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:

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:






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:

You can also view the Formatting Dates demo available in our Demonstration Library on our OTN page. The URL to out demonstration library is:

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!


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






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!


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






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:






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!


Nikos, Great post. It reminded me of some date features I noticed in the new features documentation: Basically it allows you to use some date functions in the parameters for your scheduled reports. Bryan

Posted by Bryan on June 17, 2009 at 05:23 PM MDT #

BI Publisher output: - 2009-06-26T10:42:26.164-04:00 2009-6-26 -5:0 MESSAGE_TS is from a TIMESTAMP(3) column. MESSAGE_DATE is from a TIMESTAMP(0) WITH TIME ZONE column. Why is the output different? And, how can I get the MESSAGE_DATE output with the 'T' in it?

Posted by Ron Morton on June 26, 2009 at 01:29 AM MDT #

I have questions not related to the topic that you have posted 1) Is there a standard report to list summary of the printing checks, like how many checks are printed for the day /supplier ? 2) Can copies of actual check be printed as void. Is there a standard feature possible? 3) Is it possible to switch between one step and two step publishing

Posted by MD on June 26, 2009 at 07:55 AM MDT #

When I put these two lines in the code on a report <?xdoxslt:xdo_format_date($_XDOXSLTCTX, '1913-10-25T12:00:00.000+01:00','DD-MM-YYYY HH24:MI:SS')?> <?xdoxslt:xdo_format_date($_XDOXSLTCTX, '1913-10-26T12:00:00.000+01:00','DD-MM-YYYY HH24:MI:SS')?> for result I get 25-10-1913 10:29:40 26-10-1913 11:00:00 As you can see the time part shifts. This happens with Java 1.4.2 on the server Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_04-b05) Java HotSpot(TM) Client VM (build 1.4.2_04-b05, mixed mode) With BI publisher desktop with JDK 1.6 it is ok. So be aware if you are using format-date function.

Posted by msonsuz on June 29, 2009 at 02:49 AM MDT #

Hi, I was facing the same problem in one of my reports, where the time was shifted based on the timezone mentioned in the canonical format. The requirement for the report was that a short date format should be implemented. As the developments was done in India and the deployment in another country, I should not hardcode anything in the rtf. The solution to my issue was to set the locale and the timezone in the BI publisher 'Preferences' link.

Posted by Denu Samson on July 13, 2009 at 02:55 PM MDT #

HI, Can anyone tell me what is the default timezone for BI Publisher? The BI Publiher installed in my system is taking default timezone as GMT-11.00. On what criteria is the timezone taken as default?

Posted by Ranjini on July 20, 2009 at 06:09 PM MDT #

Hi Everyone, Just an idea, has anyone tried inserting the Time/Date using MS Word instead of using SYSDATE from sql. I believe the Time/Date will get converted to correct time zone automatically. Regards Viju George

Posted by Viju George on August 26, 2009 at 06:18 PM MDT #

Hi Nikos, Thank you very much for your post. It solved my problem. With it works perfect. :)

Posted by Juan Diego Ruiz on August 27, 2009 at 10:19 PM MDT #

I need to convert dates from dd/MM/yyyy to yyyy/MM/dd in my RTF. What function can I use to do this?

Posted by Andre on February 18, 2010 at 07:13 PM MST #

Hi Andre, If your dates in the XML is shown as dd/MM/yyyy, BI Publisher treats that as a string and it will not apply any format mask you apply to it in the RTF. To address your issue, you need to go back to the source and either extract the date in the format you require (i.e. yyyy/MM/dd) and just print that to your report, or extract the date in canonical format and then apply the appropriate format mask in the RTF template.

Posted by nikos psomas on February 19, 2010 at 05:23 AM MST #

Hello all, Some of the date related issues mentioned above became more complicated when a date parameter is needed to run the report. I have a query that uses the between dates function as a condition. This is not a problem in itself, BI handles this very well, however in my case I have to truncate the date and also use the TO_DATE function. For some reason this does not work. What I had to do is use the TO_TIMESTAMP function instead of the TO_DATE function for it to work. It does work but I am wondering if I am using the right approach, having to use the 'TO_TIMESTAMP' function does not seem to be ok to represent just a regular date. Before AND begin_date BETWEEN TRUNC(TO_DATE(:P_S_FROM,'DD-MON-YY'),'YYYY') AND :P_S_TO After AND begin_date BETWEEN TRUNC(TO_TIMESTAMP(:P_S_FROM,'DD-MON-YYYY AM'),'YEAR') AND :P_S_TO Any feedback is appreacited.

Posted by Rafael JJ on May 12, 2010 at 03:53 AM MDT #

I've got to mention, that in all the time I've spent on the net, checking blogs, forums, I’ve never read one as succinct and well crafted as this one, I don't in most cases comment on personal blogs nonetheless for you personally I felt the need to make an exception, it is in fact high quality work and certainly opposite to the drivel, which i spend the majority of my time on line, reading. Thanks for bothering and investing the effort to supply your readers with a 1st class article. I count on reading alot more of your work, again thanks. Angy

Posted by Dannie Deffibaugh on August 01, 2010 at 06:49 AM MDT #

I'm working with Interview Letters in PeopleSoft Recruiting and in the Interview Template I have Date and Time of Interview. When I pull in Date it shows one day later then the actual Interview Date in the system. Thanks to you I have resolved that issue by removing formatting on that field. The other issue i'm having is Time is displaying in Military (24h) format. How can I change this to 12hr? I changed the format on the template which is a word document to h:mm AM/PM but that doesn't work. Any ideas would be greatly apprecaited. Thanks

Posted by Rupal Patel on August 27, 2010 at 01:55 AM MDT #

I am facing one issue with my xml publisher report. I need to pass the parameter "Period" in Mon-YY format. When I give a value like 'Jul-11', XML excel output displays it as 11-Jul. I tried to display with some options but it does not work.

Pls suggest me on this.


Posted by rahul maheshwari on July 22, 2011 at 02:18 AM MDT #

Hi Rupal, I'm also having exactly the same issue as yours for the military time. Did you already have an answer to that? Appreciate any feedback. Thanks so much.

Posted by Ani R on December 12, 2011 at 07:47 PM MST #

Hi Rupal, I'm also having exactly the same issue as yours for the military time. Did you already have an answer to that? Appreciate any feedback. Thanks so much.

Posted by Ani R on December 12, 2011 at 07:48 PM MST #

Post a Comment:
  • HTML Syntax: NOT allowed

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!


« July 2016