CODEWITHSUNDEEP
sqlsql-serverdatesql-server-2008-r2timestamp
Dan
Dan

Reputation: 5986

Return DATEDIFF in milliseconds on SQL Server 2008R2

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.

Upvotes: 3

Views: 1752

Answers (4)

Salman Arshad
Salman Arshad

Reputation: 272106

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).

Upvotes: 2

Hasan Mahmood
Hasan Mahmood

Reputation: 978

Datediff return int value so second will have issue with int data type.

you can get the minutes or days and multiple with 60 for getting seconds

SELECT DATEDIFF(m,{ d '1970-01-01'},getdate()) * 1000 * 60

Upvotes: 0

Eric Brandt
Eric Brandt

Reputation: 8101

Perhaps a variable?

DECLARE @milli BIGINT;

SET @milli = DATEDIFF(second,{ d '1970-01-01'},dateCompleted) * 1000.0;

SELECT @milli;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

How about using cast() or convert()?

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

Upvotes: 1

Related Questions