SQL Server datetime to bigint (epoch) overflow
Here is an example, not tested, written from free hand :)
declare @v_Date datetime
set @v_Date = '2013-03-22 15:19:02.000'
declare @v_DiffInSeconds integer
declare @v_DiffInMSeconds bigint
select @v_DiffInSeconds = DATEDIFF(s, '1970-01-01 00:00:00', @v_Date)
select @v_DiffInMSeconds = cast(@v_DiffInSeconds as bigint) * 1000 + cast(DATEPART(ms, @v_Date) as bigint)
Edit I have made this example below to illustrate the time zone conversion. The given time stamp (in seconds where I have removed the last three digits "898") is here converted to the local IST time zone by adding the 5.5 hours (19800 seconds) and I convert it back to the time stamp from local time to GMT again. Below calculations matches the values in the question (in seconds).
declare @v_time datetime
set @v_time = '1970-01-01 00:00:00'
declare @v_date datetime
set @v_date = '2013-03-22 15:19:01'
-- This returns "March, 22 2013 15:19:01"
select dateadd(s, (1363945741 + 19800), @v_time)
-- This returns "1363945741"
select datediff(s, @v_time, @v_date) - 19800
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')