0

I want to write a T-SQL function that converts a SQL Server datetime value into the number of milliseconds between the input date and 01 JANUARY 1970 00:00:00, the reference date for the javascript Date datatype.

create function jDate
(@indate datetime)
returns numeric
as
begin
 declare @datediff int;
 declare @javascriptDay1 DateTime;
 set @javascriptDay1 = cast('19700101 00:00:00.000' as DateTime);
 set @datediff = DateDiff( ms, @javascriptDay1, @indate)
 return (@datediff); 
end

Thus, if I feed the function 01 JAN 1970, it should return 0. Which it does:

declare @indate datetime
set @indate = cast('19700101 00:00:00.000' as datetime)
select dbo.jDate(@indate)

0

If I feed it 02 JAN 1970 00:00:00.000 it should returns the number of milliseconds in one day, 86400000, which it does:

declare @indate datetime
set @indate = cast('19700102 00:00:00.000' as datetime)
select dbo.jDate(@indate)

If I feed it 31 DEC 1969 23:59:59 it should return 1000, the number of milliseconds in one second, which it does.

declare @indate datetime
set @indate = cast('19691231 23:59:59.000' as datetime)
select dbo.jDate(@indate)

But if I feed it 01 JAN 1970 00:00:00.088 (i.e. only a few milliseconds difference) it should return the number of milliseconds, 88. But it returns 86.

declare @indate datetime
set @indate = cast('19700101 00:00:00.088' as datetime)
select dbo.jDate(@indate)

What causes the two millisecond error?

Tim
  • 8,669
  • 31
  • 105
  • 183
  • Check out this answer: http://stackoverflow.com/questions/634122/milliseconds-wrong-when-converting-from-xml-to-sql-server-datetime – Jerrad Jun 25 '14 at 19:02

1 Answers1

1

Per the documentation, the resolution of the DATETIME data type is rounded to increments of .000, .003, or .007 seconds.

For a higher precision, consider the DATETIME2 data type.

Rowland Shaw
  • 37,700
  • 14
  • 97
  • 166
  • Would you elaborate on how we get to `86`? If I understand the documentation's section on "accuracy" correctly, the millisecond chunk `.088` would be rounded up to .090 or down to .084. – Tim Jun 25 '14 at 19:12
  • You're dealing with two rounded values (consider `select convert(varchar(50), cast('1970-01-01 00:00:00.088' as datetime),131)`), but I would have expected the ms difference to be rounded to 87 (instead of 86), following the same rules as formatting the DATETIME, but this seems not the case, at least on the instance I tested. – Rowland Shaw Jun 25 '14 at 20:10