3

I have to store some intervals in mssql db. I'm aware that the datetime's accuracy is approx. 3.3ms (can only end 0, 3 and 7). But when I calculate intervals between datetimes I see that the result can only end with 0, 3 and 6. So the more intervals I sum up the more precision I loose. Is it possible to get an accurate DATEDIFF in milliseconds ?

declare @StartDate datetime
declare @EndDate datetime

set @StartDate='2010-04-01 12:00:00.000'
set @EndDate='2010-04-01 12:00:00.007'

SELECT DATEDIFF(millisecond, @StartDate, @EndDate),@EndDate-@StartDate, @StartDate, @EndDate

I would like to see 7 ad not 6. (And it should be as fast as possible)

** update **

I can see DATEDIFF values ending not just with 0, 3, 6 but also 4, 7 (there might be others as well) but the thing is that they are still inaccurate. The solution suggested by Alex is working. The same can be achived if you don't wan't to remember the correct datetime format with:

SELECT DATEDIFF(SECOND, @StartDate, @EndDate)*1000 + DATEPART(MILLISECOND , @EndDate) - DATEPART(MILLISECOND , @StartDate)

I still wonder why DATEDIFF(millisecond, @StartDate, @EndDate) is inaccurate ?

John Saunders
  • 160,644
  • 26
  • 247
  • 397
jomi
  • 187
  • 3
  • 10

5 Answers5

2

How about calculating the MS difference (which is accurate when you subtract DATEPARTs) then adding it to the DATEDIFF difference excluding MS?

SELECT DATEDIFF(MILLISECOND, CONVERT(VARCHAR, @StartDate, 120), CONVERT(VARCHAR, @EndDate, 120)) + DATEPART(MILLISECOND , @endDate) - DATEPART(MILLISECOND , @StartDate)

Gives 4 for .003->.007 & 7 for .000->.007

Alex K.
  • 171,639
  • 30
  • 264
  • 288
0

Or you may want use DATETIME2 date type which have more accuracy:

declare @StartDate datetime2
declare @EndDate datetime2

set @StartDate='2010-04-01 12:00:00.000'
set @EndDate='2010-04-01 12:00:00.007'

SELECT DATEDIFF(millisecond, @StartDate, @EndDate)

Selects 7 as result.

Look at DateTime2 vs DateTime in SQL Server

Community
  • 1
  • 1
Hubbitus
  • 5,161
  • 3
  • 41
  • 47
0

What about using DatePart

declare @StartDate datetime
declare @EndDate datetime

set @StartDate='2010-04-01 12:00:00.000'
set @EndDate='2010-04-01 12:00:00.007'

SELECT DATEDIFF(millisecond, @StartDate, @EndDate),
        DatePart(millisecond, @EndDate-@StartDate),
        @StartDate, @EndDate
codingbadger
  • 42,678
  • 13
  • 95
  • 110
  • @EndDate-@StartDate can only end with 0, 3 and 7 so it won't work if: set @StartDate='2010-04-01 12:00:00.003' set @EndDate='2010-04-01 12:00:00.007' – jomi Apr 27 '10 at 08:24
0

Try like this

   SELECT DATEDIFF(millisecond, @StartDate, dateadd(day,1,@EndDate)),dateadd(day,1,@EndDate)-@StartDate, @StartDate, @EndDate
Karthik
  • 3,221
  • 5
  • 28
  • 38
0

I think the issue here is that your accuracy requirements are too much for the datetime datatype.

If rounding to 6 or 7 milliseconds is an issue then you will never get the accuracy you need.

Are the intervals you have continuous? If so, could you just store a single date and then calculate the milliseconds between the first start date and the last end date?

Alternatively, can you obtain the interval using your client language? Then store the intervals as an int/long? You could possibly store the start date and the interval in milliseconds calculated in code rather than storing a start and end date in sql.

Robin Day
  • 100,552
  • 23
  • 116
  • 167
  • They are not necessarily continuous, but if they are and I sum up 5 sec intervals into an hour I see couple of 100ms are missing. Storing the intervals and the start dates could be a right solution, but I'm still curious if this is a limitation of datediff or i'm missing something here. Thanks. – jomi Apr 27 '10 at 08:38