CODEWITHSUNDEEP
sqlsql-serverxml
dreza
dreza

Reputation: 3645

Convert local datetime from xml to datetime in sql

I am receiving some data via xml that is to be inserted into an associated SQL table. The method I'm using to insert the data is by querying the XML in sql and doing a bulk insert as necessary.

The problem I have is the date is never being recognized as a valid date and so is always going back to a default date.

Here a snippet of the XML with the date provided

<Upload xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <DeviceID>0008E02B66DD_</DeviceID>
  <DeviceType>03.20</DeviceType>
  <FarmID>2</FarmID>
  <UploadDate>0001-01-01T00:00:00</UploadDate>
  <Sessions>
    <SessionID>99</SessionID>
    <RecordedDate>2012-02-03T13:00:00+13:00</RecordedDate>
    <Readings />
  </Sessions>

...

Here is the snippet of my SQL code that processes it

Select
(CASE 
 WHEN ISDATE(s.value('(RecordedDate)[1]', 'varchar(50)')) = 1 THEN s.value('(RecordedDate)[1]', 'varchar(50)')
 ELSE @UploadDate
 END) DateOfMeasurement
INTO #Session
FROM
@XMLData.nodes('/Upload') AS Upload(u)
CROSS APPLY
u.nodes('./Sessions') Sessions(s)

The field in my database I am inserting this into is a datetime field

Question:

I want to insert the date received in the XML i.e. 2012-02-03T13:00:00+13:00 into a datetime field in my SQL database. Is there anyway to convert this nicely in SQL?

Upvotes: 4

Views: 4695

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

declare @XMLData xml = '
<Upload xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <DeviceID>0008E02B66DD_</DeviceID>
  <DeviceType>03.20</DeviceType>
  <FarmID>2</FarmID>
  <UploadDate>0001-01-01T00:00:00</UploadDate>
  <Sessions>
    <SessionID>99</SessionID>
    <RecordedDate>2012-02-03T13:00:00+13:00</RecordedDate>
    <Readings />
  </Sessions>
</Upload>';

select T.N.value('substring((RecordedDate/text())[1], 1, 19)', 'datetime'),
       T.N.value('(RecordedDate/text())[1]', 'datetime'),
       T.N.value('(RecordedDate/text())[1]', 'datetimeoffset')
from @XMLData.nodes('/Upload/Sessions') as T(N);

Result:

2012-02-03 13:00:00.000 
2012-02-03 00:00:00.000 
2012-02-03 13:00:00.0000000 +13:00

Upvotes: 5

Related Questions