CODEWITHSUNDEEP
sqldatediff
jomi
jomi

Reputation: 187

SQL Server DATEDIFF accuracy

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 ?

Upvotes: 3

Views: 4347

Answers (5)

Hubbitus
Hubbitus

Reputation: 5359

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

Upvotes: 0

Alex K.
Alex K.

Reputation: 175996

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

Upvotes: 2

Robin Day
Robin Day

Reputation: 102578

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.

Upvotes: 0

Karthik
Karthik

Reputation: 3301

Try like this

   SELECT DATEDIFF(millisecond, @StartDate, dateadd(day,1,@EndDate)),dateadd(day,1,@EndDate)-@StartDate, @StartDate, @EndDate

Upvotes: 0

codingbadger
codingbadger

Reputation: 44042

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

Upvotes: 0

Related Questions