SQL Server datetime to bigint (epoch) overflow

datetime, sql-server

Solution

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

Problem

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.

Original source