Return DATEDIFF in milliseconds on SQL Server 2008R2

Question

I have a SQL query returning a value for x, which is a timestamp, mapped to a C# object of type long:

SELECT DATEDIFF(second, { d '1970-01-01'}, dateCompleted) AS x

The above statement works. However, I need to get the timestamp to return the value in milliseconds rather than seconds. In SQL Server 2016 I can do this:

SELECT DATEDIFF_BIG(millisecond, { d '1970-01-01'}, dateCompleted) AS x

...and that works great. However, I'm stuck on SQL Server 2008 R2.

I could return the values and do some post-processing in C# to multiply x by 1000 but I wondered if there's a way to handle this in the query itself. I've tried a simple multiplication but that yields an Arithmetic overflow error:

SELECT DATEDIFF(second, { d '1970-01-01'}, dateCompleted) * 1000 AS x

Could anyone suggest how to accomplish this?

Thanks.

Answer 1

DATEDIFF returns an INT so it cannot be used to return difference in millisecond if the two dates are far (approx. 25 days) apart. However you could calculate the difference in seconds, BIGINT multiply by 1000, and add the milliseconds:

SELECT DATEDIFF(SECOND, '1970-01-01', dateCompleted)
     * CAST(1000 AS BIGINT)
     + DATEPART(MILLISECOND, dateCompleted)

Assuming you want UNIX timestamp you also need to add the timezone offset to the result (I hope you stored it along with date completed).

Answer 2

How about using cast() or convert()?

SELECT DATEDIFF(second,{ d '1970-01-01'},dateCompleted) * convert(bigint, 1000) AS x