1. Using the Oracle Mapper with Oracle Integration Generation 2
  2. Mapper Use Cases
  3. Perform Date Conversions in the Mapper

Perform Date Conversions in the Mapper

You can perform date conversion tasks in the mapper such as converting dates in different time zones, formats, and timestamps.

Convert Date from One Time Zone to Another Time Zone

For example, to convert between India Standard Time (IST) and Greenwich Mean Time (GMT):

  • IST to GMT:
    fn:adjust-dateTime-to-timezone(xsd:dateTime('2021-05-20T10:49:07.859+05:30'), xsd:dayTimeDuration('PT0H'))
  • GMT to IST:
    fn:adjust-dateTime-to-timezone(xsd:dateTime('2021-05-20T10:00:00Z'), xsd:dayTimeDuration('PT5H30M'))

Convert Date from One Format to Another Format

Format Definition XSLT Mapping Explanation
  • Source Format: DD-MON-YYYY
    19-Nov-2021
  • Target Format: YYYY-MM-DD
    2021-11-19
 
<xsl:variable name="monthListByNum">
<months>
<month id="1" value="jan"/><month id="2" value="feb"/><month id="3" value="mar"/><month id="4" value="apr"/>
<month id="5" value="may"/><month id="6" value="jun"/><month id="7" value="jul"/><month id="8" value="aug"/>
<month id="9" value="sep"/><month id="10" value="oct"/><month id="11" value="nov"/><month id="12" value="dec"/> 
</months>
</xsl:variable>

<xsl:value-of select="concat (substring-after (substring-after ($srcDateString, '-' ), '-' ), '-',  
$monthListByNum/months/month[@value=fn:lower-case (substring-before (substring-after 
($srcDateString, '-' ), '-' ) )]/@id, '-', substring-before ($srcDateString, '-' ) )" 
xml:id="id_18"/>
 Use an XSLT variable to hold the MON to MM conversion.

Use string manipulation to get the correct format.

Add and Subtract Dates from the Current Date Time

  • If the data is in xsd:dateTime or IS0-8601 or (YYYY-MM-DDTHH:MM:SS+TZ) format:
    • Add 10 days to the current date time:
      fn:current-dateTime() + xsd:dayTimeDuration('P10D')
    • Add one year to the current date time:
      fn:current-dateTime() + xsd:yearMonthDuration('P1Y')
    • Subtract 10 days from the current date time:
      fn:current-dateTime() - xsd:dayTimeDuration('P10D')
    • Subtract one year from the current date time:
      fn:current-dateTime() - xsd:yearMonthDuration('P1Y')
  • If the data is in xsd:date or YYYY-MM-DD format:
    • Convert the data to xsd:dateTime format using xsd:dateTime(concat($inputDate,'T00:00:00')).
  • If the data is in any other format:
    • Convert the data to xsd:dateTime format using the xp20:format-dateTime() function or string functions such as concat(), substring-before(), or substring-after().

Convert dateTime to and from Epoch Time

  • If the data is in xsd:dateTime or IS0-8601 or (YYYY-MM-DDTHH:MM:SS+TZ) format:
    • Convert the current dateTime to epoch time:
      (fn:current-dateTime() - xsd:dateTime('1970-01-01T00:00:00')) div xsd:dayTimeDuration('PT1S')
    • Convert epoch time to dateTime:
      (xsd:dateTime('1970-01-01T00:00:00') + ($epochTime * xsd:dayTimeDuration('PT1S')))
  • If the data is in xsd:date or YYYY-MM-DD format:
    • Convert the data to xsd:dateTime format using xsd:dateTime(concat($inputDate,'T00:00:00')).
  • If the data is in any other format:
    • Convert the data to xsd:dateTime format using the xp20:format-dateTime() function or string functions such as concat(), substring-before(), or substring-after().

Convert a Date Timestamp to a UNIX Timestamp

(fn:current-dateTime() - xsd:dateTime('1970-01-01T00:00:00')) div xsd:dayTimeDuration('PT1S')

Convert a Month Number to a Month Name

If you have the exact date and time, you can use the format-dateTime function. 

xp20:format-dateTime ((fn:current-dateTime ( ), "[MNn" )