Formatting time in BI Publisher

This article describes a mechanism to format time in BI Publisher.

Let me explain with an example

I have a XML file with the following tag in it.

          <SsStartTime>18:00:00</SsStartTime>

The requirement is to display this time as 6:00PM

This value is displayed in the start column of the following table

I found the following code at one of the places to get this done

<?xdofx:if to_number(substr(SsStart,1,2))<=12 then to_number(substr(SsStart,1,2))+10-10 else to_number(substr(SsStart,1,2))-12 end if?><?concat(':',substring(SsStart,4,2))?><?xdofx:if to_number(substr(SsStart,1,2))<12 then ' AM' else ' PM' end if?>

While this is a good effort, I think it is too tough to develop this kind of logic esp if you are as lazy as I am :)

So here is what I did.

      1.       Concatenated my time string with a hardcoded date

      2.       Converted the whole string into canonical format

      3.       Used msword date format to get the desired result

My code:

<?psfn:getCanonicalDate(concat('01/01/2012 ',SsStartTime))?>

Here SsStartTime is the node that holds the time component. '01/01/2012 ' is a random date. Note the space in the end of the date. The space is reqd to get the correct output from getCanonicalDate function. getCanonicalDate is a Siebel extension function which returns the date in the canonical format. We obviously have to include the namespace for using this. Below is the namespace declaration for siebel extension functions.

<?namespace:psfn=http://www.oracle.com/XSL/Transform/java/com.siebel.xmlpublisher.reports.XSLFunctions?>

Check this link to know more about using functions defined in a jar by including namespaces in rtf files

If the date is not in the canonical format then msword date formatting cannot be applied. If you are not developing BI Publisher reports for Siebel then form the canonical date string by the using the usual concat function  and format masks for date conversions. Canonical format is 'YYYY-MM-DD' or 'YYYY-MM-DDTHH:MI:SS'

Once this is done, use msword form field properties as shown in the screenshots above. Select h:mm am/pm as the format and Date as the type.

Till next time