我正在使用C#程序处理日期。

我想过滤具有DateTime,DateTime2,DateTimeOffset列的任何表。

我将LastRefreshDate作为DateTimeOffSet存储在UTC中,并用它来过滤那些表上的数据。我根据用于在这些表中存储日期的时区来调整LastRefreshDate的偏移量(使用NodaTime)。通常,它是由用户给出的。

因此,我创建了一个测试样本来解释问题。通常,SQL查询是动态的,参数也是动态的。这是示例代码:

[TestMethod]
public void Test()
{
    using (SqlConnection connection = new SqlConnection("Server=myserver;Database=mydb;User ID=admin;Password=admin"))
    {
        connection.Open();
        using (SqlCommand command = new SqlCommand("SELECT [TimeStamp] FROM  [dbo].[DATA] WHERE [TimeStamp] >= @p0", connection))
        {
            string datestring = "2019-06-18 13:35:20.1133868 -04:00";

            // Does not work
            // DateTimeOffset p0 = DateTimeOffset.Parse(datestring, CultureInfo.InvariantCulture);
            // Does work
            DateTime p0 = DateTime.Parse(datestring, CultureInfo.InvariantCulture);
            command.Parameters.AddWithValue("@p0", p0);
            using (SqlDataReader reader = command.ExecuteReader())
            {
                var dataTable = new DataTable();
                dataTable.Load(reader);
                var result = dataTable.Rows.Count == 0;
            }
        }
    }
}


我创建了2个SQL小提琴来演示该问题。顺便说一句,我运行了SQL Server Profiler,生成的查询类似于小提琴中的查询。

DateTime小提琴:http://sqlfiddle.com/#!18/a06be/1

declare @p0 datetime = '2019-06-18 13:35:20'
SELECT
    [TimeStamp]
FROM
    [dbo].[DATA]
WHERE
    ([TimeStamp] >= @p0)


DateTimeOffSet小提琴:http://sqlfiddle.com/#!18/a06be/2

declare @p0 datetimeoffset(7) ='2019-06-18 13:35:20.1133868 -04:00'
SELECT [TimeStamp]
FROM
    [dbo].[DATA]
WHERE
    ([TimeStamp] >= @p0 )


我做了更多的测试。通过直接应用强制类型转换,SQL查询将起作用。似乎SQL Server implicit conversion的行为方式与显式强制转换不同。这是测试用例:

declare @p0 datetime
set @p0 = '2019-06-18 17:48:00.00'
declare @p1 datetimeoffset(7)
set @p1 = '2019-06-18 17:47:00.5385563 -04:00'

select 1
where @p0 > cast(@p1 as datetime) -- working
--where @p0 > @p1                       -- not working

最佳答案

一些东西:


在SQL Server中,如果使用CASTCONVERT而不指定样式,则默认样式为0,当将datetimeoffset转换为datetimedatetime2时,该样式仅使用日期和datetimeoffset中的时间值,而不考虑偏移量。如果要考虑偏移量,请使用CONVERT并将1用作样式:

DECLARE @p0 datetimeoffset = '2019-06-18 13:35:20.1133868 -04:00'
SELECT convert(datetime, @p0, 0) as 'A', convert(datetime, @p0, 1) as 'B'
-- A = 2019-06-18T13:35:20.113Z
-- B = 2019-06-18T17:35:20.113Z

当使用datetime参数查询datetime2datetimeoffset字段时,在隐式转换中确实考虑了偏移量(就像上面的B一样)。
在C#端,请注意DateTime.Parse。默认情况下,提供偏移量时,它将发出基于本地时间的值。如果您进行检查,则会看到p0.Kind == DateTimeKind.Local。您可以传递DateTimeStyles.AdjustToUniversal,但是更好的主意是将其解析为DateTimeOffset,就像您在“无效”代码中显示的一样。但是,而不是传递完整的DateTimeOffset,而是传递UtcDateTime属性:

DateTime p0 = DateTimeOffset.Parse(datestring, CultureInfo.InvariantCulture).UtcDateTime;

出于性能和稳定性方面的考虑,您可以考虑使用ParseExactTryParseExact代替Parse。或者,因为您说过您已经在使用Noda Time,所以可以将其文本解析功能与OffsetDateTimePattern一起使用。从那里您可以调用.ToDateTimeOffset().UtcDateTime.ToInstant().ToDateTimeUtc()
或者,您可以将SQL数据库列定义为datetimeoffset,然后可以传递任何DateTimeOffset参数,并且在查询时会将其标准化为UTC。

10-08 13:40
查看更多