将XML中的本地日期时间转换为SQL日期时间

Question

我正在通过XML接收一些数据，这些数据将被插入到相关的SQL表中。我使用的方法是通过在SQL中查询XML并根据需要执行批量插入来插入数据。

我的问题是日期从未被识别为有效日期，因此始终返回默认日期。

以下是提供日期的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>

...

这是我的 SQL 代码片段，用于处理它：

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)

• 注意: @UploadDate 实际上是 SQL 的 getDate() 函数的返回值，即我的默认值

我要插入到数据库中的字段属于 datetime

问题:

我想将 XML 中收到的日期，即 2012-02-03T13:00:00+13:00，插入到 SQL 数据库的 datetime 字段中。有没有办法在 SQL 中很好地转换这个日期？

- dreza

Answer 1

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);

结果：

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

- Mikael Eriksson