Date Formats in OIC

Date Formats in OIC is little complex (not really), but gets a little tricky at times . Many developers get stuck and need to refer documentations for figure out the right format and conversion for their requirement.

Here we will see the most common requirements with Date handling in Oracle integration cloud.

Default Date format in OIC

Below is the default date format in OIC . It’s of ISO8601 Standard

"CreationDate": "2021-03-04T11:18:33+00:00"

Date Time Conversions

There can be many scenarios for conversions ,we can use function(xp20:format-dateTime) for converting to desired formats. Lets list in a Table.

The OIC function(xp20:format-dateTime) takes two arguments 1. datetime 2. format

The standard use of this function is

Fusion Format
xp20:format-dateTime(dateTime,“[Y0001]-[M01]-[D01]”)When sending Date to Oracle SaaS
Rest Api with Date field this format can be used
dd-MM-yyyy HH:mm:ssxp20:format-dateTime(dateTime,‘[D01]-[M01]-[Y0001] [H01]:[m01]:[s01]
MM/dd/yyyy HH:mm:ssxp20:format-dateTime(dateTime,’[M01]/[D01]/[Y0001] [H01]:[m01]:[s01]‘)
yyyy-MM-dd h:mm AM/PMxp20:format-dateTime(dateTime,’[Y0001]-[M01]-[D01] [h1]:[m01] [P]‘)
HH:mm TimeZonexp20:format-dateTime(dateTime,’ [H01]:[m01] [z]‘)
ordinal value of the dayxp20:format-dateTime(/nsmpr0:schedule/nsmpr0:startTime,’ [Dwo]’)10 will show Tenth
name of the Monthxp20:format-dateTime(/nsmpr0:schedule/nsmpr0:startTime,’ [MNn]’)e.g March
Get the day of the weekxp20:format-dateTime(/nsmpr0:schedule/nsmpr0:startTime,’ [FNn]’)e.g Monday
Change current date format to <MMM-YYYY>xp20:format-dateTime(string(fn:current-dateTime()),'[MNn,3-3]-[YYYY]’)e.g APR-2020

OIC to Fusion Date Format Conversion

Oracle Fusion has a different date format so date in OIC need to be converted before sending to fusion.

When Sending Date from OIC to Fusion Web Services use
Your OIC mapping would be something like this
xp20:format-dateTime ($getEmpDetails/nsmpr2:executeResponse/ns28:response-wrapper/ns28:items/ns28:CreationDate, "[Y0001]-[M01]-[D01]" )

Conversion using XSLT

Sometime if you do not have a standard conversion format you can use XSLT to read the input date and convert to required format.

Example Change Date format from DD/MM/YYYY to ‘YYYY-MM-DD’

Make use of below XSLT in the mapping

concat(substring-after($DATE, ‘/’),’/’), ‘-‘, substring-before(substring-after($DATE, ‘/’),’/’),’-‘,substring-before($DATE, ‘/’))
Make sure to replace the $DATE to the XPath as per your input XML

Creating a Unique Identifier from Current DateTime

There are many times you may need to concatenate Date time to your entity fields like Batch Number , Receipt number , invoice number etc. for testing and uniqueness. (E.g InvoiceYearDateTime)

By default if you concatenate your field with date time function fn:current-dateTime() it will print like this


Mostly this value will not be accepted by the Fusion Rest , Soap Apis, so we would need to format it.

I use day-from-dateTime() and seconds-from-dateTime() function to achieve this.

Hope you liked this post on handling date formats in OIC.

concat('Invoice',string(fn:day-from-dateTime(fn:current-dateTime())), string(fn:seconds-from-dateTime(fn:current-dateTime())))
For Unique Identifer Creation
concat('Invoice',string(fn:day-from-dateTime(fn:current-dateTime())), string(fn:seconds-from-dateTime(fn:current-dateTime())))

Oracle Documentation on Dates

Suggested Read : Calling ESS Job In OIC


We will be happy to hear your thoughts

      Leave a reply

      Cloud Technologies Blog
      Compare items
      • Total (0)
      Shopping cart