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.| 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 |
Good work
ReplyDeleteThank you :)
DeleteGood Job !!
ReplyDeleteThank you :)
DeleteVery nice and detailed explanation !!
ReplyDeleteThank you :)
Deletei want to convert to 24hr date format ,
ReplyDeletexp20:format-dateTime('2019-09-11T10:16:31.943+05:30', '[D01]/[M01]/[Y001] [H01]:[m01] .
The above expression is not working : (
I tried the same expression and it works perfectly.
Deletexp20:format-dateTime('2019-09-11T15:16:31.943+05:30', '[D01]/[M01]/[Y001] [H01]:[m01]')
Date
ReplyDeleteHow can I add 1 day to a custom date ?
ReplyDeleteExample:
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.
Hi,
DeleteThe 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'.
Thank you! That worked. The main issue was time T00:00:00 was not included with my date string.
DeleteGood to hear, it worked 😊
DeleteHi I wanted to add seconds in my date, date format is : 03-02-2022 12:50:23
ReplyDeleteso 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 ?
Hi Nikhil,
DeleteThe 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.
Hi Pranav, thanks for your great article.
ReplyDeleteI 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?
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
ReplyDelete"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.