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.
Dates
Table of Contents
Handling Different Date Formats In OIC
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
xp20:format-dateTime(DateTime,Format)
| REQUIRED DATE FORMAT OUTPUT | FUNCTION TO BE USED | COMMENTS |
|---|---|---|
| Fusion Format yyyy-mm-dd | 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 | xp20:format-dateTime(dateTime,‘[D01]-[M01]-[Y0001]‘) | |
| dd-MM-yyyy HH:mm:ss | xp20:format-dateTime(dateTime,‘[D01]-[M01]-[Y0001] [H01]:[m01]:[s01]‘ | |
| MM/dd/yyyy HH:mm:ss | xp20:format-dateTime(dateTime,’[M01]/[D01]/[Y0001] [H01]:[m01]:[s01]‘) | |
| yyyy-MM-dd h:mm AM/PM | xp20:format-dateTime(dateTime,’[Y0001]-[M01]-[D01] [h1]:[m01] [P]‘) | |
| HH:mm TimeZone | xp20:format-dateTime(dateTime,’ [H01]:[m01] [z]‘) | |
| ordinal value of the day | xp20:format-dateTime(/nsmpr0:schedule/nsmpr0:startTime,’ [Dwo]’) | 10 will show Tenth |
| name of the Month | xp20:format-dateTime(/nsmpr0:schedule/nsmpr0:startTime,’ [MNn]’) | e.g March |
| Get the day of the week | xp20: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 xp20:format-dateTime(dateTime,"[Y0001]-[M01]-[D01]") 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
Invoice2021-05-05T11:03:49.907Z
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())))concat('Invoice',string(fn:day-from-dateTime(fn:current-dateTime())), string(fn:seconds-from-dateTime(fn:current-dateTime())))Suggested Read : Date comparison in oic
