Tuesday 29 October 2013

Formatting Date in Oracle XML(BI) Publisher

We can format dates in BI Publishes in the following three different ways,

1. Formatting Date using Microsoft Word’s native date format mask.
2. Formatting Date using Oracle’s format-date function.
3. Formatting Date using Oracle’s abstract date format masks.
4. Format with Calendar.

Lets me now explain little details about the above three ways, and below is the XML I am going to use in the below examples,
1. Formatting Date using Microsoft Word’s native date format mask?
In the BI Publisher Properties window, select type as Date and select the format from the drop down box.
Here is the output sample of all the three types of date’s we had in the XML. Format I selected is
DD-MM-YY.
2. Formatting Date using Oracle’s format-date function.
format-date() is an inbuilt function in BI Publisher.
Syntax:

In the above syntax MASK is an optional parameter. We will see more details about MASK later in this post.

To use this format-date function, under the BI Publisher Properties window -> keep type as Regular Text -> click on Advanced tab –> call format-date function with the tag name. (For time being we will not pass the MASK parameter.)
Here is the output sample of all the three types of date’s we had in the XML.
3. Formatting Date using Oracle’s abstract date format masks.
In this section we will seeing various MASK parameters that can be passed to MASK’
?> function. - See more at: http://flexfields.blogspot.ae/2011/01/bi-publisher-formatting-date.html#sthash.LrUuM9zp.dpuf3. Formatting Date using Oracle’s abstract date format masks.
In this section we will seeing various MASK parameters that can be passed to
Function.

Out of box we have the following MASK available for various format of dates,
Mask Constant                                               Output
SHORT                                                          1/26/11
MEDIUM                                                      Jan 26, 2011
LONG                                                           Wednesday, January 1, 2011
SHORT_TIME                                              1/26/11 8.10PM
MEDIUM_TIME                                          Jan 26, 2011 8.10PM
LONG_TIME                                                Wednesday, January 1, 2011 8.10PM
SHORT_TIME_TZ                                       1/26/11 8.10PM GMT
MEDIUM_TIME_TZ                                   Jan 26, 2011 8.10PM GMT
LONG_TIME_TZ                                        Wednesday, January 1, 2011 8.10PM GMT


The default MASK constant is MEDIUM.

In order to user these abstract’s you need to have your XML date data in canonical format as,
YYYY-MM-DDThh:mm:ss(+/-)HH:MM
(For example check out DATE_UTC tag value in the above XML.)

Here,
YYYY is the year
MM is the month
DD is the date
T is the separator between the date and time component
hh is the hour in 24-hour format
mm is the minutes
ss is the seconds
(+/-)HH:MM is the time zone offset from Universal Time (UTC) or Greenwich Mean Time

Here is the output for our XML data using the MEDIUM_TIME mask,
Now if you notice the output that we got earlier while using format-date with the default parameter, column for date_utc alone is showing Jan 27 were as the date we have in the XML is 26.

This is because of the time zone offset –08:00.

Where this timezone offset is nothing but, the time zone difference of the date that we passed in the XML with respect to GMT. So BI Publisher will automatically understand the timezone of the XML data, and print/convert it as per the BI Publisher Local Server time.

How to get Time zone offset of the data?
Ok now we know its important to pass the time zone offset in the XML data to use the MASK. Lets see how to get them in the XML data,

First Option:
If your data source is a data definition file, then no need to worry, the standard BI Publisher data definition engine by default will give the data in the canonical format with time zone offset. So you are good. No need of any extra coding.

If you do not want the template to change the date as per the time zone then, user the TO_CHAR function to print without the time zone offset. So that template engine will not modify the date as per the server time zone.

Second Option:
Lets say your data source is an RDF file. In that case use TO_CHAR function in your SQL query and bring the data in required canonical format.

Third Option:
Lets say, you do not have control over the data source. You have only access to the template and know what will be the time zone of the data that is going in come in the XML.
4. Format with Calendar:
Now that we had seen different types of formatting dates, let finally see how to use the above formats with various calendars.
Here is the supported calendar types,
- GREGORIAN
- ARABIC_HIJRAH
- ENGLISH_HIJRAH
- JAPANESE_IMPERIAL
- THAI_BUDDHA
- ROC_OFFICIAL (Taiwan)


I think, I had covered all the date formatting techniques in BI Publisher. If you feel I had missed anything or would like to explain some specific example, feel free to put on the comments.



Ur's
AmarAlam

26 comments:

Unknown said...

Hi Amar

This as a great help. Just one question. I have a date in format DD-MON-YY so the date manipulation is not working. Is there an easy way to convert this date to the canonical format

Thanks Nicki

Unknown said...

Good Blog, For training on Oracle BI Publisher, Check this site for trainings on emerging IT courses . Go here if you’re looking for training on Oracle BI Publisher.

Unknown said...
This comment has been removed by the author.
Unknown said...

Hello Above mentioned Third Solution NOT working in my case

Report shows Date-1
Eg 13-Mar-16 in report Displays as Mar 12,2016

Anonymous said...

Very nice to wroth and reading.Thanks for sharing.

Oracle Weblogic Server Training

Peter Johnson said...

Thanks For Posting.I had Learn Lot from this Blog Post.Coming to Our self,We are the leading provider of Restaurant Equipment Parts in US.Really thanks For Posting.Have A Nice Day.

seo said...

Awesome! Superbly Written by the blogger just loved his way of posting in the blog and also happy for sharing this about our service called Urgent care in chicago.happy for sharing the information.I just Want to share the blog to my friends and family members.

Priya said...

Great Post!!! I have read your blog and it's very informative & really impressed .
Thanks for sharing it. Keep continue your post.
Best SAP online Training Institute in India

SAP HANA online Training

SAP S/4 HANA training online

SAP UI5 Online Training in India

SAP FICO TRAINING ONLINE

Unknown said...

This Blog is very helpful and useful, came to know that I should be strong in my basics and this blog helps me to improve it, US Medical Residency in Chicago Services Provided by Us. Thanks For Posting. I Am refereed by my friend to this blog and I also want to refer my other friends to this blog.

Unknown said...

I have read your article it's very informatic and beneficial Mulesoft Training thanks for posting.

svrtechnologies said...

Thanks For Sharing Such an Important and Useful Content On Salesforce Certification Training

24Layouts said...

Really Thanks For Sharing We Offer Services on plots for sale in vizag

Unknown said...

Thanks for such an detailed Explanation......

Just NewsTo U 99

jyothi kits said...

Wow it is really wonderful and awesome.
Tableau Training
Teradata Training

MOUNIKA said...

Enjoyed every bit of your article post. Great.
c-language-Training

c-online-Training

sandhyakits said...

Very good brief and this post helped me alot. Say thank you I searching for your facts. Thanks for sharing with us!
msbi Online Training

mule-esb Online Training

sumathikits said...

nice article thanks for sharing the great infomation...!
Android Training

BlockChain Training

Data Science Training

Dot Net Training

IBM Integration Bus Training

IBM Message Broker Training

svrtechnologies said...

thanks for posting such an useful info...

ibm integration bus tutorial

nhuthuy said...

Thanks for sharing, nice post! Post really provice useful information!

An Thái Sơn với website anthaison.vn chuyên sản phẩm máy đưa võng hay máy đưa võng tự động tốt cho bé là địa chỉ bán máy đưa võng giá rẻ tại TP.HCM và giúp bạn tìm máy đưa võng loại nào tốt hiện nay.

Jack sparrow said...




Thank you for sharing such a great information.Its really nice and informative.hope more posts from you. I also want to share some information recently i have gone through and i had find the one of the best mulesoft 4 training videos


Deepa said...

Interesting to read. Salesforce Training Sydney is a best institute.

svrtechnologies said...

I just loved your article on the beginners guide to starting a blog. Thank you for this article. c training and java tutorial videos with highly experienced faculty.

veera said...

I just loved your article on the beginners guide to starting a blog.If somebody take this blog article seriously in their life, he/she can earn his living by doing blogging.thank you for thizs article | Certification | Cyber Security Online Training Course|

Ethical Hacking Training Course in Chennai | Certification | Ethical Hacking Online Training Course|

CCNA Training Course in Chennai | Certification | CCNA Online Training Course|

RPA Robotic Process Automation Training Course in Chennai | Certification | RPA Training Course Chennai|

SEO Training in Chennai | Certification | SEO Online Training Course





jdgvks said...

Hello Admin!

Thanks for the post. It was very interesting and meaningful. I really appreciate it! Keep updating stuffs like this.
hadoop training in bangalore

oracle training in bangalore

hadoop training in acte.in/oracle-certification-training">oracle training

oracle online training

oracle training in hyderabad

hadoop training in chennai

Anonymous said...

Thankyou..
Android Training in Bangalore

Android Training

Android Online Training

Android Training in Hyderabad

Android Training in Chennai

Android Training in Coimbatore

Subhagruha Projects said...

Excellent blog. Lots of useful information here, thanks for your effort!
Real Estate Plots in Vizag

Post a Comment