Introduction

One of the most common requirement while working with Data Columns having DATE datatype is to get the correct format. While some prefer ‘MM-DD-YYYY’ some regions use ‘DD-MM-YYYY’ and others might be comfortable with ‘YYYY-MM-DD’. When working with Oracle HCM Cloud UI Pages users may set their preferences (read complete article here) explicit conversion mechanism might be required when this data is to be passed as a BIP Report.

An individual has two options in this case:

1. Use SQL Functions in SQL Query to get desired format

2. Use Template (RTF/XPT) specific formatting.

In case of Oracle HCM Cloud many a time the SQL Based Date Functions like TO_CHAR (), TO_DATE () are not honored and they still return data in a specific format.

In such case, we may make use of the FORMAT MASK feature available.

Let us try to have a look at a worked example

Worked Example

We would create a very simple data model for this purpose.

Once the Data Model is created and we try to have a look at the Sample Data it will appear

Next, we would need to create a Report Template in which we will fill the following details:

On the Template, this setting would be found under the Data Formatting Options as shown:

The Report would then be generated.

Summary

This is how we can use different format mask on the Date Data Type to get different date formats as required by the external 3rd party system.

As of today only Microsoft and Oracle are the two Formatting Style Supported and one would need to key in (Free Flow Text) a valid date combination for the Report to display correct dates. There is currently no validation in place and in case an incorrect combination is entered the Report would go into error.

Please do try different combinations  (valid ones of course) and do share your findings.

That’s it from me for the time being. Good bye till next time, have a great day and do feel free to send your feedbacks/review comments on this article.

Ashish Harbhajanka

Add comment