Sql Server Datetime To Bigint (epoch) Overflow

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 ti

Solution 1:

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 integerdeclare@v_DiffInMSeconds bigintselect@v_DiffInSeconds = DATEDIFF(s, '1970-01-01 00:00:00', @v_Date)
select@v_DiffInMSeconds =cast(@v_DiffInSeconds asbigint) *1000+cast(DATEPART(ms, @v_Date) asbigint)

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

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

SELECTcast(DATEDIFF(s, '1970-01-01 00:00:00.000', '2016-12-09 16:22:17.897' ) asbigint)

Use the below statement to get the time in milliseconds.

SELECTcast(DATEDIFF(s, '1970-01-01 00:00:00.000', '2016-12-09 16:22:17.897' ) asbigint) *1000

convert epoch to human readable date time using below statement:

selectDATEADD(s, 1481300537, '1970-01-01 00:00:00')

Solution 3:

Epoch to datetime

createfunction[dbo].[EpochToDate](@Date bigint)
returnsdatetimebeginreturn (select dateadd(s, @Date, '19700101'))
end

Solution 4:

Use below code to get human readable date from epoch time

selectDATEADD(s,convert(bigint,@date)/2, DATEADD(s, convert(bigint,@date)/2, '1970-01-01 00:00:00'))