Saturday, 13 April 2019

Change date time format in Oracle Integration Cloud

We got a request from multiple folks saying they are struggling to change the date time format in Oracle Integration Cloud.

We are writing this article to see how to change the default date format(2019-04-13T05:09:05.049+00:00) to a different format.

Oracle Integration Cloud Service provides an Out of the box function(xp20:format-dateTime) which allows us to change the date time format. We are on ICS version (18.4.3.0.3) and the function is available(xp20:format-dateTime)

Let us see how to apply this function and change the date format as per the requirement

The function(xp20:format-dateTime) takes below two arguments:
  1. dateTime: Provide the DateTime input parameter as a string which needs to be the format
  2. Format: Provide the format as a string as per the requirement
Example-1: Parse the date time in the format "dd-MM-yyyy", below will be the syntax:

xp20:format-dateTime(dateTime,'[D01]-[M01]-[Y0001]')

Example-2: Parse the date time in the format "dd-MM-yyyy HH:mm:ss", below will be the syntax:

xp20:format-dateTime(dateTime,'[D01]-[M01]-[Y0001] [H01]:[m01]:[s01]'

Example-3: Parse the date time in the format "MM/dd/yyyy HH:mm:ss", below will be the syntax:

xp20:format-dateTime(dateTime,'[M01]/[D01]/[Y0001] [H01]:[m01]:[s01]')

Example-4: Parse the date time in the format "yyyy-MM-dd h:mm AM/PM", below will be the syntax:

xp20:format-dateTime(dateTime,'[Y0001]-[M01]-[D01] [h1]:[m01] [P]')

Example-5: Parse the date time in the format "HH:mm TimeZone", below will be the syntax:

xp20:format-dateTime(dateTime,' [H01]:[m01] [z]')

Example-6: Get the ordinal value of the day, below will be the syntax:

xp20:format-dateTime(/nsmpr0:schedule/nsmpr0:startTime,' [Dwo]')

Example-7: Get the name of the Month in the capital, below will be the syntax:

xp20:format-dateTime(/nsmpr0:schedule/nsmpr0:startTime,' [MNn]')

Example-8: Get the day of the week, below will be the syntax:

xp20:format-dateTime(/nsmpr0:schedule/nsmpr0:startTime,' [FNn]')

Below are the explanations for all the formatting codes in the example:

Value
Description
D01
Produces the two-digit day
M01
Produces the two-digit month
Y0001
Produces the four-digit year
H01
Produces the 2-digit hour in a 24-hour clock
m01
Produces the 2-digit minutes
s01
Produces the 2-digit seconds
h1
Produces the 1- or 2-digit hour in a 12-hour clock
P
Generates a.m. or p.m.
z
Produces the time zone
MNn 
Generates the capitalized name of the month.
For example: If we are sitting in April month, it will produce “April
FNn
The capitalized name of the day of the week
For example: If today is Saturday, it will produce “Saturday
Dwo
The word for the ordinal value of the day
For example: If today is 13th April, it will produce “thirteenth
  • Let's go ahead and create a scheduler process in ICS and create 8 variable as per the example was given above

  • Print all 8 variables in the different logger. Activate the scheduler and execute
  • Open the activity stream and see the values of all 8 variables. Notice the start time(since we have given the start time as an input of the function) and accordingly the result of each format. 


No comments:

Post a Comment