DateTime rounding error

[gardnerr]

Hi,

We've had an issue with a parameterised query on SQL Server returning data for the wrong dates. I've found that this parameter:

new DateTime(2019, 11, 20, 23, 59, 59, 999)

actually converts to 2019-11-21 00:00:00 when in fact it should be 2019-11-20 23:59:59.999

Interestingly, new DateTime(2019, 11, 20, 23, 59, 59, 998) converts to 2019-11-20 23:59:59.997.

I'd imagine it's related to the conversion to ticks. This is on 2.0.30.

Thanks

Rob

[stuartbright]

Are you using the SQL Server datetime data type? It rounds values to increments of .000, .003, or .007 seconds as documented here: https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15#rounding-of-datetime-fractional-second-precision

[gardnerr]

The field I am querying is a datetime and I changed to datetime2 and got the same behavior. This is the query I get in the profiler:

exec sp_executesql N'select * from stock where updatedate between @start and @end;',N'@start datetime,@end datetime',@start='2019-11-20 00:00:00',@end='2019-11-21 00:00:00'

So the conversion to 21/11/19 is the issue.

[duyn9uyen]

@gardnerr, did you ever find a solution? I'm having the same issue as you described.

[duyn9uyen]

Are you using the SQL Server datetime data type? It rounds values to increments of .000, .003, or .007 seconds as documented here: https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15#rounding-of-datetime-fractional-second-precision

This is interesting. A way around this for me was to use a datetime of 23.59.59.997 instead of 23.59.59.999 to avoid this round up.

[robodobdob]

I hit this issue today too. A DateTime of "2022-11-09 23:59:59.999" was being converted to "2022-11-10 00:00:00.000" causing date-based queries to return incorrect data. Changing the stored procedure to use datetime2 didn't make a difference.

In the end, I ran the dates through a filter method which stripped the milliseconds component (we only need to go to seconds resolution) by doing this:

DateTime.Parse(outStartDate.ToString("yyyy-MM-dd hh:mm:ss.000"))