正确地格式化XML DateTime上的日期时间

I am trying to serialize an entity using XML serialization on SQL. Unfortunately I notice that my datetime is not properly read. I am returning a 24 hour format, but on SQL, its reading a different time. Here is the exact SQL Stored Procedure:

我正在尝试使用SQL上的XML序列化来序列化一个实体。不幸的是，我注意到我的datetime没有被正确地读取。我返回的是24小时格式，但是对于SQL，它的读取时间不同。下面是准确的SQL存储过程:

DECLARE
@XML XML = '- <ArrayEntityView>
                - <entEntity>
                        <dtModifiedDate>2017-07-24T14:09:20.4483795+08:00</dtModifiedDate> 
                  </entEntity>
                </ArrayEntityView>'

        DECLARE @tblProcAssign TABLE 
          ( 

             dtmoddate    DATETIME
          ) 

        INSERT INTO @tblProcAssign 
                    (
                    dtmoddate
                     ) 
        SELECT entuserunits.value('dtModifiedDate[1]', 'DATETIME') AS dtModDate 
        FROM   @XML.nodes('ArrayEntityView/entEntity')entUserUnits(entuserunits) 


        select * from @tblProcAssign

Based on the code above, I am passing a date:

根据上面的代码，我正在传递一个日期:

2017-07-24T14:09:20.4483795+08:00

But on SQL, its reading is:

但在SQL上，它的读数是:

2017-07-24 06:09:20.447

And I am not sure why, it should be:

我不知道为什么，应该是:

2017-07-24 14:09:20.447

The data came from c# code DateTime.Now. I don't want to use GetDate in SQL since sometimes, I am not using DateTime.Now. How should I format this entity or any parsing I need to do so that I will get my expected result?

数据来自c#代码日期。我不想在SQL中使用GetDate，因为有时我不使用DateTime.Now。我应该如何格式化这个实体或我需要做的任何解析，以得到我预期的结果?

1 个解决方案

#1

You need to switch the data type from datetime to datetimeoffset if you want time zone information to be recognised. Without it, only the actual time value (that is, UTC) is returned.

如果希望识别时区信息，则需要将数据类型从datetime切换到datetimeoffset。没有它，只返回实际的时间值(即UTC)。

Make sure that the data type is replaced in both the table definition and the XML value() method:

确保在表定义和XML值()方法中都替换了数据类型:

DECLARE @tblProcAssign TABLE (dtmoddate datetimeoffset);

INSERT INTO @tblProcAssign (dtmoddate) 
SELECT entuserunits.value('dtModifiedDate[1]', 'datetimeoffset') AS dtModDate 
FROM @XML.nodes('ArrayEntityView/entEntity')entUserUnits(entuserunits);

#1