Friday, July 11, 2008

Playing with XSLT: Convert string to dateTime

Seems that most of us feel difficulty in dealing with date & time in XSLT.
PART-1
Problem: This post tells you about how to map a string source column to a date destination column.
Assumptions:
To acheive it I created dummy table in Database,
Table name: Test
SourceColumn name: a_str
SourceColumn Value: 20080514
Destination column name: a_dt

Solution:
Lets say,
XPath Expression: /top:testCollection/top:test/top:a_str=$d

To make the database understand about the date, we need to transform the source value. For that we need to use chain of functions.
Analyse the solution given below:

<ns2:a_dt>
<xsl:value-of select='concat(substring($d,1.0,4.0),"-",substring($d,5.0,2.0),"-",substring($d,7.0,2.0))'/>
</ns2:a_dt>

Bingo...It works.


PART-2
Problem: To map a string source column (containing date and time) to a date destination column.
Assumptions:
To acheive it I created dummy table in Database,
Table name: TestSource
Column name: a_strSource
Column Value: 20080514111213
Destination column name: a_dt


Solution:
Lets say,
XPath Expression: /top:testCollection/top:test/top:a_str=$d

To make the database understand about the date and Time, we need to transform the source value. For that we need to use chain of functions. Notice that a "T" is concatinated in the code. This one is for adding the Timezone.
Analyse the solution given below:

<ns2:a_dt>
<xsl:value-of select="concat(substring($d,1.0,4.0),"-",substring($d,5.0,2.0),"-",substring($d,7.0,2.0),'T',substring($d,9.0,2.0),":",substring($d,11.0,2.0),":",substring($d,13.0,2.0))"/>
</ns2:a_dt>

Bingo...It works.

1 comment:

Chandra said...

Abhishek ,

The article you have written is still not converting it to date type . I think the data is still in string . Please correct me if I am wrong .

Thanks,
Chandra