CODEWITHSUNDEEP
sqlsql-servert-sql
Jonathan Wood
Jonathan Wood

Reputation: 67355

Rounding problems with DATETIME

The following queries:

DECLARE @__dateRange_StartDate_4 DATETIME ='2021-03-01T00:00:00.000'
DECLARE @__dateRange_EndDate_3 DATETIME ='2021-03-31T23:59:59.999'

SELECT DATEDIFF(DAY, '2021-03-01T00:00:00.000', '2021-03-31T23:59:59.999') + 1

SELECT DATEDIFF(DAY, @__dateRange_StartDate_4, @__dateRange_EndDate_3) + 1

SELECT @__dateRange_EndDate_3

Produces the following results:

31

32

2021-04-01 00:00:00.000

It appears @__dateRange_EndDate_3 is being rounded to the next day, which I don't want.

What is the correct way to have the second SELECT return 31?

Note: My queries are actually being called from Entity Framework so I may be limited to what I can do here, but I at least want to understand the issue as this was unexpected.

Upvotes: 1

Views: 1299

Answers (2)

marc_s
marc_s

Reputation: 755541

DATETIME in SQL Server has an accuracy of 3.33ms (0.003 seconds) - therefore, the "highest" possible value for March 31, 2021 would be 2021-03-31T23:59:59.997 - anything beyond that will be rounded up to the next day.

This is just one of the reasons why as of SQL Server 2008 the general recommendation is to use DATE for when you don't need any time portion, or DATETIME2(n) (when you need the time portion; n is the number of fractional digits after the second - can be 0 through 7) datatypes.

DATETIME2(n) offers accuracy down to 100 ns and thus 2021-03-31T23:59:59.999 will be absolutely no problem in a DATETIME2(3) column.

As an added benefit, DATETIME2(n) also doesn't have this "arbitrary" lower limits of supported dates only through 01.01.1753 - with DATETIME2(n) you can store any date, back to 1 AD

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1271231

This is silly. Don't bother with trying to get the last increment before a time -- and learning that datetime is only accurate to 0.003 seconds.

Express the logic only using dates:

DECLARE @__dateRange_StartDate_4 DATE ='2021-03-01'
DECLARE @__dateRange_EndDate_3 DATE ='2021-04-01'

SELECT DATEDIFF(DAY, '2021-03-01', '2021-04-01');

SELECT DATEDIFF(DAY, @__dateRange_StartDate_4, @__dateRange_EndDate_3);

SELECT @__dateRange_EndDate_3;

Then use these with inequalities:

WHERE date >= @__dateRange_StartDate_4 AND
      date < @__dateRange_EndDate_3

Inequalities -- with >= and < is the recommended way to handle date/time comparisons. Dealing with the "last increment" problem is only one of the problems it solves.

If you really are committed to figuring out the last increment before midnight, you can use DATETIME2 or .997. But I don't recommend either of those approaches. Here is a db<>fiddle.

Upvotes: 1

Related Questions