DATEIFF SQL Server Accuracy

Question

I need to save some intervals in mssql db. I know that datetime accuracy is approx. 3.3 ms (only 0, 3, and 7 can end). But when I calculate the intervals between dates, I see that the result can only end with 0, 3 and 6. Thus, the more intervals I summarize, the more accuracy is lost. Is it possible to get the exact 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 announcement not 6. (And it should be as fast as possible)

** update **

I can see the DATEDIFF values ​​ending not only 0, 3, 6, but also 4, 7 (there may be others), but the fact is that they are still inaccurate. The solution proposed by Alex works. The same can be achieved if you do not want to remember the correct date and time format:

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

, DATEDIFF(millisecond, @StartDate, @EndDate) ?

Answer 1

MS ( , DATEPART s), DATEDIFF, MS?

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

4 .003 → . 007 7 .000 → . 007

Answer 2

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

Answer 3

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

Answer 4

, , datetime.

6 7 , .

? , , ?

Alternatively, can you get the interval using your client language? Then save the intervals as int / long? You could save the start date and interval in milliseconds calculated in the code, and not store the start and end date in sql.

Answer 5

Or you can use the DATETIME2 date type, which has more precision:

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 the result.

See DateTime2 vs. DateTime in SQL Server