CODEWITHSUNDEEP
sql-serverxml
junaib
junaib

Reputation: 21

Date conversion in a XML column in SQL Server

How do I convert the below mentioned XML code date format.

<StartDate>2015-12-24T00:00:00</StartDate>
<EndDate>2015-12-29T15:39:20</EndDate>

Upvotes: 0

Views: 4347

Answers (4)

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

This may help you

DECLARE @X XML ='<StartDate>2015-12-24T00:00:00</StartDate>
<EndDate>2015-12-29T15:39:20</EndDate>'


SELECT   @X.value('/StartDate[1]','DATETIME') AS START_DTE
        ,@X.value('/EndDate[1]','DATETIME') AS END_DTE

+-------------------------+-------------------------+
|        START_DTE        |         END_DTE         |
+-------------------------+-------------------------+
| 2015-12-24 00:00:00.000 | 2015-12-29 15:39:20.000 |
+-------------------------+-------------------------+

Update: From comments

The Datatime format in XML follows the ISO8601 standards. And you are thinking to format it native SQL format, which is not correct that you are treating XML like normal text data. The data present in XML format is correct. And If you want you can convert it to native SQL as above mentioned.

There is a good info at Wikipedia ISO 8601(Combined date and time representations) on how the XML hold date time data.

A single point in time can be represented by concatenating a complete date expression, the letter T as a delimiter, and a valid time expression. For example, "2007-04-05T14:30".

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

There are several correct answers, but I've got the feeling, that these answers don't hit your actual issue:

In my table there is a xml column, and that xml column contains somuch data including date, And i want to update those dates to date format like '2/28/2017' now the date format is like '2012-04-26T00:00:00'

If I got you correctly you want to change the stored dates within your XML to another format, correct?

Simple answer: Don't!

ISO8601 is the standard format for date/time values within XML. The format you would like more 2/28/2017 is culture related and could lead to errors, or even worse!, to wrong values, if day and month both are below 13: 04/05/2017 can be taken as 4th of May or as 5th of April. You should never rely on culture settings!

XML is not meant to be human readable. Or in better words: It is meant to be human readable for technical people only... It is a standardizes string representation of structured, complex documents. The format of values should not bother you! Use an appropriate editor for the presentation.

Upvotes: 1

Mehrnoosh
Mehrnoosh

Reputation: 899

This might be work

declare @date ='2015-12-24T00:00:00 2015-12-29T15:39:20'
SELECT CONVERT(date, Left(@date,10)) as NewDate

Upvotes: 0

marc_s
marc_s

Reputation: 754258

If you're accessing your XML content with the built-in XQuery functionality, you can just use the .value() method and define the output datatype to be a DATETIME2(3) type - no special treatment necessary:

DECLARE @InputTbl TABLE (ID INT NOT NULL, XmlContent XML)

INSERT INTO @InputTbl (ID, XmlContent)
VALUES (1, '<Root>
<StartDate>2015-12-24T00:00:00</StartDate>
<EndDate>2015-12-29T15:39:20</EndDate>
</Root>');

SELECT
    StartDate = XC.value('(StartDate)[1]', 'datetime2(3)'),
    EndDate = XC.value('(EndDate)[1]', 'datetime2(3)')
FROM
    @InputTbl
CROSS APPLY
    XmlContent.nodes('/Root') AS XT(XC)

This returns this output:

enter image description here

Upvotes: 2

Related Questions