Filtering using a DateTimeOffset as a parameter issue

Question

I was playing with dates using a C# program.

I want to filter on any table that has a DateTime, DateTime2, DateTimeOffset columns.

I store the LastRefreshDate as DateTimeOffSet in UTC and I use it to filter data on those tables. I adjust the offset (using NodaTime) of the LastRefreshDate based on the timezone used to store the dates in those tables. Usually, it is given by the user.

So I created a test sample to explain the problem. Usually, the SQL queries are dynamic and the parameters as well. Here is the sample code:

[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;
            }
        }
    }
}

I created 2 SQL fiddles that demonstrate the issue. By the way, I ran the SQL Server Profiler and the generated queries are similar to the queries in the fiddles.

DateTime fiddle: http://sqlfiddle.com/#!18/a06be/1

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

DateTimeOffSet fiddle: 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 )

I did even more tests. By applying the cast directly, the SQL query works. It seems that SQL Server implicit conversion is not behaving in the same manner as an explicit cast. Here is the test case:

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

user_634824 · Answer

A few things:

In SQL Server, if you use CAST, or CONVERT without specifying a style, the default style is 0, which when converting a datetimeoffset to either a datetime or datetime2 simply takes the date and time value from the datetimeoffset without considering the offset. If you want to take the offset into account, then use CONVERT and pass 1 for the style:
```
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
```
When querying a datetime or datetime2 field using a datetimeoffset parameter, the offset is indeed taken into account in the implicit conversion (it is like B above).
On the C# side, be careful about DateTime.Parse. By default, it emits a local time based value when an offset is provided. If you check, you'll see p0.Kind == DateTimeKind.Local. You could pass DateTimeStyles.AdjustToUniversal, but a better idea is to parse as a DateTimeOffset like you showed in your "doesn't work" code. But then instead of passing the full DateTimeOffset, pass the UtcDateTime property:
```
DateTime p0 = DateTimeOffset.Parse(datestring, CultureInfo.InvariantCulture).UtcDateTime;
```
For both performance and stability reasons, you might consider using ParseExact or TryParseExact instead of Parse. Or, since you said you already are using Noda Time, you can use its text parsing features with an OffsetDateTimePattern. From there you'd either call .ToDateTimeOffset().UtcDateTime, or .ToInstant().ToDateTimeUtc().
Alternatively, you could just define your SQL database columns as datetimeoffset, then you can pass any DateTimeOffset parameter and it will be normalized to UTC when querying.

Filtering using a DateTimeOffset as a parameter issue

1 Answers