代码之家 › 专栏 › 技术社区 › billybob

使用datetimeoffset作为参数问题进行筛选

nodatime timezone-offset timezone sql-server c#

billybob · 技术社区 · 7 年前

我在用C程序玩约会。

我要筛选任何具有datetime、datetime2、datetimeoffset列的表。

我存储 LastRefreshDate 作为 DateTimeOffSet 在UTC中,我使用它来过滤那些表中的数据。我调整(使用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;
            }
        }
    }
}

我创建了两个SQL坐标来演示这个问题。顺便说一下,我运行了SQL Server事件探查器,生成的查询与fiddle中的查询类似。

日期时间小提琴: http://sqlfiddle.com/#!18/a06be/1

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

日期时间偏移小提琴: 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

0 回复 | 直到 7 年前

Matt Johnson-Pint 7 年前

一些事情:

在SQL Server中,如果使用 CAST 或 CONVERT 不指定样式,默认样式为 0 ,在转换 datetimeoffset 至 datetime 或 datetime2 只需从 日期时间偏移量 不考虑偏移。如果要考虑偏移量,请使用 转换 然后传球 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
```
当查询 日期时间 或 日期时间2 字段使用 日期时间偏移量 参数,在隐式转换中确实考虑了偏移量(就像 B 以上)。
在C面,小心 DateTime.Parse . 默认情况下,它会发出 当地时间 提供偏移量时的基值。如果你查一下,你会发现 p0.Kind == DateTimeKind.Local . 你可以通过 DateTimeStyles.AdjustToUniversal 但更好的方法是将 DateTimeOffset 就像你在“不工作”代码中显示的那样。但不是通过满分 日期时间偏移量 ,通过 UtcDateTime 属性:
```
DateTime p0 = DateTimeOffset.Parse(datestring, CultureInfo.InvariantCulture).UtcDateTime;
```
出于性能和稳定性的原因,您可以考虑使用 ParseExact 或 TryParseExact 而不是 Parse . 或者,由于您说过已经在使用noda时间,您可以使用它的文本分析功能 OffsetDateTimePattern . 从那里你要么打电话 .ToDateTimeOffset().UtcDateTime 或 .ToInstant().ToDateTimeUtc() .
或者,您可以将SQL数据库列定义为 日期时间偏移量 ,然后你可以通过任何 日期时间偏移量 参数,查询时将规范化为UTC。