Format date from YYYYMMDD to insert into SQL Server 2

[IS300]

I am using XML bulk load to insert data into our SQL server database. Everything was going okay until our source for the xml files decided to change the way the date was inputed. Before, it would be

<reportDate>July 27, 2004</reportDate>

Now it is

<reportDate>20040727<reportDate>

XML bulk load doens't like this, so my script keeps on failing. Is there any way to formate this date properly using XSL or another method? Thanks,

 

[jaybytez]

The second output that you showed is not well-formed. The reportDate closing tag is wrong. Not sure if it is a typo or reflection of the processing.

But here is a template to change the reportDate element format:

<xsl:template match="reportDate">
  <xsl:variable name="month" select="number(substring(.,5,2))"/>
  <xsl:choose>
    <xsl:when test="$month=1">January</xsl:when>
    <xsl:when test="$month=2">February</xsl:when>
    <xsl:when test="$month=3">March</xsl:when>
    <xsl:when test="$month=4">April</xsl:when>
    <xsl:when test="$month=5">May</xsl:when>
    <xsl:when test="$month=6">June</xsl:when>
    <xsl:when test="$month=7">July</xsl:when>
    <xsl:when test="$month=8">August</xsl:when>
    <xsl:when test="$month=9">September</xsl:when>
    <xsl:when test="$month=10">October</xsl:when>
    <xsl:when test="$month=11">November</xsl:when>
    <xsl:when test="$month=12">December</xsl:when>
    <xsl:otherwise>Invalid</xsl:otherwise>
  </xsl:choose>
  <xsl:text disable-output-escaping="yes">&amp;npsb;</xsl:text>
  <xsl:value-of select="number(substring(.,7,2))"/>,
  <xsl:text disable-output-escaping="yes">&amp;npsb;</xsl:text>  
  <xsl:value-of select="number(substring(.,1,4))"/>  
</xsl:template>

Hope that helps

-jay

 

[chiph]

Both date values that they're sending you are not valid XML. The W3C says you need to use ISO-8603 format:
[tab]yyyy-mm-ddThh:nn:ss

In your case, they need to send you something that looks like:

<reportDate>2004-07-27T00:00:00</reportDate>

SQL Server will not have a problem with this format.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[IS300]

Thanks jay. Had to modify what you gave me a bit (I didn't need the month), but it works great!

 

[jel]

Please, take Chips' post seriously.
Don't mess with date-formats, stick to standards as much as you can!

 

[chiph]

Ooops. Made a mistake in my post. The applicable standard is actually ISO-8601.

This is also an Internet standard. RFC-3339.

Chip H.

____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[jel]

;-)
Even so: I still think it is important to stick to standards as much as you can.

 

[IS300]

The thing is that the XML document that I am inputing into SQL Server is not created by us, so I have no control over how they do their date format. I would prefer to stick to a standard as well.