CONVERT DATE INTO SQL format USING XSLT function

Question

Input Date format : 2019-07-09-10.56.24.147431

Desired Date Format: 09-JUL-19

I need to do the above in XSLT (version 1.0)

I tried the below :

<xsl:value-of select="concat (substring (xp20:format-dateTime(substring(/ns0:outbound/ns0:cancel_wrk/ns0:hdr_data/ns0:ts_ext, 1,10),'[DD]-[MNn,3-3]-[YYYY]'), 1,6 ), substring (year-from-date (date(substring(/ns0:outbound/ns0:cancel_wrk/ns0:hdr_data/ns0:ts_ext, 1,10))), 3 ) )"/>

 <tns:xtrnlSysDttm>

<xsl:value-of select="concat (substring (xp20:format-dateTime(substring(/ns0:outbound/ns0:cancel_wrk/ns0:hdr_data/ns0:ts_ext, 1,10),'[DD]-[MNn,3-3]-[YYYY]'), 1,6 ), substring (year-from-date (date(substring(/ns0:outbound/ns0:cancel_wrk/ns0:hdr_data/ns0:ts_ext, 1,10))), 3 ) )"/> 

  </tns:xtrnlSysDttm>

Error message while inserting into SQL database with the result of the above XSLT:

BINDING.JCA-12563 Exception occurred when binding was invoked. Exception occurred during invocation of JCA binding: "JCA Binding execute of Reference operation 'insert' failed due to: DBWriteInteractionSpec Execute Failed Exception. insert failed. Descriptor name: [GBE_INT_CSS_PQ_PQFFEFO.PqffefoProxy]. Caused by Exception [EclipseLink-3001] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.ConversionException Exception Description: The object [25-Aug-19], of class [class java.lang.String], could not be converted to [class java.sql.Timestamp]. Internal Exception: BINDING.JCA-11635 Could Not Convert Date Exception. Unable to convert a string value in the xml to a java.sql.Date. Even though databases accept strings representing dates in a variety of formats, the adapter only accepts strings representing dates in xml ISO date format. The input value must be in the iso 8601 date format YYYY-MM-DD.

Ajeet Singh Ajeet Singh · Accepted Answer · 2019-08-26T07:03:28

I assume Input Data

<root>
    <input_date>2019-07-09-10.56.24.147431</input_date>
</root>

for XSL:-

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" exclude-result-prefixes="xs" version="1.0">

<xsl:template match="/">
    <xsl:call-template name="MMM-DD-YYY">
        <xsl:with-param name="date" select="'input_date'"/>
    </xsl:call-template>
</xsl:template>

<xsl:template name="MMM-DD-YYY">
    <xsl:param name="date"/>

    <xsl:variable name="year" select="substring(substring-before(root/input_date, '-'), 3,2)"/>
    <xsl:variable name="day" select="substring-before(substring-after(substring-after(root/input_date, '-'), '-'), '-')"/>
    <xsl:variable name="month">
        <xsl:call-template name="month-abbr">
            <xsl:with-param name="month" select="substring-before(substring-after(root/input_date, '-'), '-')"/>
        </xsl:call-template>
    </xsl:variable>

    <xsl:value-of select="concat($day,'-',$month,'-',$year)"/>
</xsl:template>

<xsl:template name="month-abbr">
    <xsl:param name="month"/>
    <xsl:choose>
        <xsl:when test="$month = '01'">JAN</xsl:when>
        <xsl:when test="$month = '02'">FEB</xsl:when>
        <xsl:when test="$month = '03'">MAR</xsl:when>
        <xsl:when test="$month = '04'">APR</xsl:when>
        <xsl:when test="$month = '05'">MAY</xsl:when>
        <xsl:when test="$month = '06'">JUN</xsl:when>
        <xsl:when test="$month = '07'">JUL</xsl:when>
        <xsl:when test="$month = '08'">AUG</xsl:when>
        <xsl:when test="$month = '09'">SEP</xsl:when>
        <xsl:when test="$month = '10'">OCT</xsl:when>
        <xsl:when test="$month = '11'">NOV</xsl:when>
        <xsl:when test="$month = '12'">DEC</xsl:when>
    </xsl:choose>
</xsl:template>
</xsl:stylesheet>

Get Output

09-JUL-19

CONVERT DATE INTO SQL format USING XSLT function

1 Answers