Date-Time Functions Conversion Formatting Add & Subtract in OIC

         In this topic we are going to discuss on how to use different build-in DateTime Functions in Oracle Integration Cloud OIC. Also we will try to understand how to change the date or time format from one to another and conversion of data type related to DateTime.

1. All build-in functions in OIC.
2. Formatting of Date-Time from one to another.
3. Convert dataType to Date-Time.
4. Add & Subtract DateTime.

• Functions in OIC.   

A.      There are Date functions in Oracle Integration Cloud for which we don't required to pass any argument. So we have created variables and assigned those date functions ( type cased with string() function ). Find the below examples, how assignments are configured and there corresponding results. - current-date() , current-time() , current-dateTime() , implicit-timezone()

The variable names are similar to the function name.

See how current-date + current-time created the current-dateTime.

• Expression : string(fn:current-date())
• Expression : string(fn:current-time())
• Expression : string(fn:current-dateTime())
• Expression : string(fn:implicit-timezone())

B.     Now we will use the values of above mentioned date time functions as input to other functions as listed below. 

• day-from-date -  current-date() is the argument of this function.
• day-from-dateTime -  current-dateTime() is the argument of this function.
• Expression : string(fn:day-from-date(fn:current-date()))
• Expression : string(fn:day-from-dateTime(fn:current-dateTime()))

• Similarly other functions of this category are -
• day-from-date   - $arg as current-date()
• day-from-dateTime  - $arg as current-dateTime()
• hours-from-dateTime   - $arg as current-dateTime()
• hours-from-time   - $arg as current-time()
• minutes-from-dateTime   - $arg as current-dateTime()
• minutes-from-time   - $arg as current-time()
• month-from-date   - $arg as current-date()
• month-from-dateTime   - $arg as current-dateTime()
• seconds-from-dateTime   - $arg as current-dateTime()
• seconds-from-time   - $arg as current-time()
• year-from-date    - $arg as current-date()
• year-from-dateTime     - $arg as current-dateTime()
• timezone-from-date    - $arg as current-date()
• timezone-from-time   - $arg as current-time()
• timezone-from-dateTime     - $arg as current-dateTime()

C.    Now let's check on the Duration functions and its use in Oracle Integration Cloud. 

        Before that let's understand on xsd:duration. Find the below details on duration type.

        The duration data type is used to specify a time interval.

         The time interval is specified in the following form " PnYnMnDTnHnMnS" where:

•          P indicates the period ( required)
•          nY indicates the number of years
•          nM indicates the number of months
•          nD indicates the number of days
•          T indicates time section start( required if specify hours, minutes, or seconds)
•          nH indicates the number of hours
•          nM indicates the number of minutes
•          nS indicates the number of seconds
•          Example like P5Y4M3DT2H1M10S = 5Yrs, 4Month, 3Days, 2Hrs, 1Min, 10Sec.

        So, that's all about fundamentals of Duration dataType, 

        Now note down all duration related functions and it's use cases.

• Find the below example how  years-from-duration has been configured.
• Casting $arg to an xs:yearMonthDuration ie $Duration variable.
• Expression for Duration variable : 'P5Y4M'
• Expression: string(fn:years-from-duration(xsd:yearMonthDuration($Duration)))

• days-from-duration   -casting $arg to an xs:dayTimeDuration , returns integer.
• hours-from-duration   -casting $arg to an xs:dayTimeDuration , returns integer.
• minutes-from-duration -casting $arg to an xs:dayTimeDuration, returns integer.
• months-from-duration -casting $arg to an xs:yearMonthDuration,returns integer.
• seconds-from-duration  -casting $arg to an xs:dayTimeDuration,returns integer.

D.    Few adjustment functions for time-zone are described as below.

• adjust-dateTime-to-timezone - 

      fn:adjust-dateTime-to-timezone (xs:dateTime ("2007-07-07T10:00:00"), xs:dayTimeDuration ("-PT10H")). 

      Results in: 2007-03-07T07:00:00-10:00.

• timezone-from-date, timezone-from-time- $arg current-date & current-time.

• Date Formatting in OIC.         

         Oracle Integration Cloud also provides a date time function named as 'xp20:format-dateTime' to change the format of dateTime to another one.                        

• You need to convert your dateTime to xsd:dateTime format if you are not using current-dateTime function as an input to this.
• Remember this function takes both the inputs as 'string'.
• There are many formatting expression you can use in this function. Please search in internet to get your required format.
• Below expressions might help you a bit.
• string(xp20:format-dateTime($Current_DateTime,'[Y0001]/[MNn]/[D01]'))

Value	Expression	Value	Expression
2012	[Y0001]	2012	[Y]
12	[Y01]	Two Thousand and Twelve	[YWw]

Value	Expression	Value	Expression
08	[M01]	8	[M]
VIII	[MI]	August	[MNn]
AUGUST	[MN]	Aug	[MNn,*-3]
AUG	[MN,*-3]

Value	Expression	Value	Expression
05	[D01]	5	[D] or [D1]
31st	[D1o]	Tuesday	[FNn]

Value	Expression	Value	Expression
3	[h]	9	[H]
08	[H01]	03	[m01]
3	[m]	3	[s]
PM	[PN]	Am	[Pn]

• Date Conversion in OIC.    

         Oracle Integration Cloud also provides type casting function to convert your string type data to dateTime or date type data. The function takes input as string and gives output as expected format. To use this function, your input data should be like xsd:dateTime formatted and type casted as string.

Now, lets say we have one schema element in xsd which is xsd:dateTime type and our input is coming as string type but in same format. In such cases we can type case our variable to expected format.

The Schema element looks like below.

• Add & Subtract DataTime OIC.    

        Oracle Integration Cloud also provides feature of addition and subtraction of DateTime as per your requirement. It's as simple as using mathematical operator ( +/- ). We can add or subtract required day, time, month, year from current date-time. If the dateTime is not current date-time then you need to modify it and convert it to xsd:dateTime format. Let's see the below example.

• Add 1 Day 2 Hours from current date-time.
• string(fn:current-dateTime() + xsd:dayTimeDuration("P1DT2H"))
• Result : 2020-08-03T11:54:32.5Z 
• For Year or Month to add, use function xsd:yearMonthDuration()

• Subtract 1 month from currect dateTime.
• string(fn:current-dateTime() -xsd:yearMonthDuration("P1M"))
• Check the current dateTime and subtracted dateTime.

 

Here comes the end of the basic understand of different Date Time Functions in Oralce Integration Cloud. Feel free to post your comments/suggestions/queries. I shall be happy to respond to those. 

Thank You . Happy Learning !!