DateTime formatting using xp20:format-dateTime ()

xs:dateTime Format

In XML, we generally use xs:dateTime(xs:xmlns="http://www.w3.org/2001/XMLSchema") data type to store date time data. The generic representation of date time, that is supported by xs:dateTime in xml:

YYYY-MM-DDThh:mm:ss[.SSS][Z|(+|-)hh:mm]

Example

2019-09-11T10:16:31.943+05:30

Code Description Example
YYYY represents a 4-digit year 2019
MM represents a 2-digit month 09
DD represents 2-digit date 11
hh represents 2-digit hours 10
mm represents minutes in 2-digits 16
ss represents seconds in 2-digits 31
SSS represents milliseconds 943
T represents time separator T
Z represents UTC time zone or 0 time zone offset
(+|-)hh:mm represents time zone offset +5:30

Few more valid examples: 2019-09-11T21:32:52, 2019-09-11T21:32:52+02:00, 2019-09-11T19:32:52Z, 2019-09-11T19:32:52+00:00, 2019-09-11T21:32:52.11179

While working with different systems, we may come across such systems, which require date time in some different formats other than default format as supported by xs:dateTime. To over come such scenarios, function is available in OIC(Oracle Integration Cloud) and Oracle SOA

Format DateTime

xp20:format-dateTime($dateTime as string, $format as string).
xmlns:xp20="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.Xpath20"

$dateTime should contain date time value as per xs:dateTime data type as as string eg: '2019-09-11T10:16:31.943+05:30'
$format should contain output format to which we need to convert to. For e.g. if we want output to be '11/09/2019' then value of $format should be '[D01]/[M01]/[Y001]'.
xp20:format-dateTime('2019-09-11T10:16:31.943+05:30', '[D01]/[M01]/[Y001]')

To format current date time, we can use xp20:current-dateTime() in $dateTime
xp20:format-dateTime(xp20:current-dateTime(), '[D01]/[M01]/[Y001]')

Formatting Codes:

Below are some important codes which can be helpful in generating desired format for target application.
Sample dateTime: 2019-09-03T15:16:31.943+05:30
Code Description Example Output
[Y001] 4 digit year 2019
[Y01] 2 digit year 19
[M01] 2 digit month 09
[D01] 2 digit date 03
[H01] 2 digit hour in 24 hour clock 15
[h01] 2 digit hour in 12 hour clock 03
[m01] 2-digit minutes 16
[s01] 2-digit seconds 31
[f001] 3-digit milliseconds 943
[Z] Time zone offset in the form (+|-)hhmm +0530
[z] Time zone offset in the form GMT(+|-)hh:mm GMT+05:30
[Dwo] Word for ordinal value of day third
[dwo] The day of the year, represented in ordinal words second hundred and forty-sixth
[MNn] Capitalized month name September
[P] am/pm pm
[PX] Uppercase am/pm PM
[FNn] Capitalized weekday name Tuesday
[MNn,a-b] Capitalized month name having atleast "a" charcters and atmost "b" characters [MNn,3-3]: Sep


Comments

  1. Very nice and detailed explanation !!

    ReplyDelete
  2. i want to convert to 24hr date format ,
    xp20:format-dateTime('2019-09-11T10:16:31.943+05:30', '[D01]/[M01]/[Y001] [H01]:[m01] .

    The above expression is not working : (

    ReplyDelete
    Replies
    1. I tried the same expression and it works perfectly.
      xp20:format-dateTime('2019-09-11T15:16:31.943+05:30', '[D01]/[M01]/[Y001] [H01]:[m01]')

      Delete
  3. How can I add 1 day to a custom date ?
    Example:
    Assign Variable Input_Date = "2022-02-11"
    I would like to add 1 day to the above date string and get the result
    "2022-02-12"

    I tried the following:
    xp20:format-dateTime( string( xsd:dateTime( $Input_Date) + xsd:dateTime( 'P1D')), '[Y0001]-[M01]-[D01]')

    I got the error
    The XPath expression failed to execute, the reason was : FORG0001: invalid value for cast/constructor.

    ReplyDelete
    Replies
    1. Hi,

      The expression which you have mentioned has few errors.
      'P1D' is not dateTime but duration and also "2022-02-11" is not a valid xsd:dateTime format. As mentioned in the blog dateTime should also have time included with date, then this conversion will work. One way you can achieve is:

      xp20:format-dateTime( string(xsd:dateTime( '2022-02-11T00:00:00') + xsd:dayTimeDuration('P1D')), '[Y0001]-[M01]-[D01]')

      In this you may need to concat input date with 'T00:00:00'.

      Delete
    2. Thank you! That worked. The main issue was time T00:00:00 was not included with my date string.

      Delete
    3. Good to hear, it worked 😊

      Delete
  4. Hi I wanted to add seconds in my date, date format is : 03-02-2022 12:50:23
    so I tried this :

    string(xp20:format-dateTime($Start_Date_Time,"[Y0001]-[M01]-[D01]T[H01]:[m01]:[s01]")+xsd:dayTimeDuration("PT2M30S"))

    but its giving error (XPath expression failed to execute), can u please help ?

    ReplyDelete
    Replies
    1. Hi Nikhil,

      The date "03-02-2022 12:50:23" is not in a valid xs:dateTime format, due to which format date time will not work. And date time duration will work only on dateTime. One alternative could be to use combination of substring and concat to convert to xs:dateTime format.

      Delete
  5. Hi Pranav, thanks for your great article.

    I have another question. Is it possible to localize xp20:format-dateTime?

    For example, if I use [D1] [MNn] [Y0001] I would get a date formatted as "1 May 2022", but I would like to represent it in Dutch, eg. "1 Mei 2022".

    Is that possible?

    ReplyDelete
  6. Hi Everyone, I'm trying to get a -12 hours data from database. I'm using below function to get a current data and time
    "xp20:format-dateTime($InputVariable.DbSchedulingTblCollection/ns5:DbSchedulingTbl/ns5:createdTs,'[Y001]-[M01]-[D01] [H01]:[m01]:[s01]')".
    Now my current requriement is that I change the time form current time to -12 Hours.

    ReplyDelete

Post a Comment

Popular posts from this blog

Create Delimited String from XML Nodes and Vice Versa in SOA 12c

Import and Export MDS artifacts in SOA 12c