SQL Server datetime to bigint (epoch) overflow

Question

I have a 'datetime' column with value 2013-03-22 15:19:02.000

I need to convert this value into epoch time and store it in a 'bigint' field

The actual epoch value for the above time is, 1363945741898, when I use

  select DATEDIFF(s, '1970-01-01 00:00:00', '2013-03-22 15:19:02.000')

I get, 1363965542, when I use

select DATEDIFF(ms, '1970-01-01 00:00:00', '2013-03-22 15:19:02.000')

I get,

Msg 535, Level 16, State 0, Line 1 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

How to get the exact epoch value from the 'datetime' field

I use SQL Server 2008. Also this should work with 2005.

Answer 1

Epoch to datetime

create function [dbo].[EpochToDate](@Date bigint)
returns datetime
begin
    return (select dateadd(s, @Date, '19700101'))
end 

Answer 2

When tried to get exact milliseconds we get the overflow exception. we can get the values till seconds and multiply with 1000.

This is equivalent to new Date().getTime() in javascript:

Use the below statement to get the time in seconds.

SELECT cast(DATEDIFF(s, '1970-01-01 00:00:00.000', '2016-12-09 16:22:17.897' ) as bigint)

Use the below statement to get the time in milliseconds.

SELECT cast(DATEDIFF(s, '1970-01-01 00:00:00.000', '2016-12-09 16:22:17.897' ) as bigint) * 1000

convert epoch to human readable date time using below statement:

select DATEADD(s, 1481300537, '1970-01-01 00:00:00')