More on Time!
By Antony Reynolds-Oracle on May 12, 2008
More on Time!Seems that dateTime is one of the most frustrating types in XML. I just had a colleague ask how to convert a string to an XML dateTime type. There are a rich range of functions to convert dateTimes to strings, but a paucity to do the reverse. So I present here one approach to this. I use XSLT variables to help in the parsing. Here is the XSLT fragment that converts a US formatted date (MM/DD/YYYY) to an XML dateTime format (CCYY:MM:DDTHH:MM:SS). Note that in an XML dateTime all fields are mandatory, the only optional pieces are the decimal part of the seconds (CCYY:MM:DDTHH:MM:SS.SS) and the timezone portion (CCYY:MM:DDTHH:MM:SSZ or CCYY:MM:DDTHH:MM:SS+02:00).
In the fragment below I take the input data from XPath expression /DateList/SingleDate/DateField. This has the date in format MM/DD/YYYY. I use this input to populate an element dateitem which is of dateTime type.
Lets go throught the XSLT a little at a time.
<!-- Parse Date of format MM/DD/YYYY -->
<!-- I declare a variable TempInput to hold everything after the first '/' in the date (DD/YYYY). -->
<!--I set a variable Month to hold the month, generated by picking up everything before the first '/' in the date (MM). -->
<xsl:value-of select='substring-before(/imp1:DateList/imp1:SingleDate/imp1:DateField, "/")'/>
<!--I define a variable Day to hold the day portion which I get by picking up everything before the '/' in my Temp variable (DD). -->
<xsl:value-of select='substring-before($TempInput, "/")'/>
<!--I then pick up everything after the '/' in Temp variable (YYYY) and put it in a Year variable. -->
<xsl:value-of select='substring-after($TempInput, "/")'/>
<!--Finally I construct an XML dateTime format by concating the Year, Month and Day variables in the correct XML format. -->
Obviously the parsing could be made to handle time formats and timezones as well.
Hoping that this helps someone struggling with parsing text strings into dates.
I have created a simple ESB project that takes data from a file in US date format and loads it into two columns in a database table, one formatted as a string the other as a SQL Date. When using the Database adapter the SQL Date format gets converted to an XML dateTime. The project is available for download here.