CODEWITHSUNDEEP
sqlsql-serverdatetime
Varun K
Varun K

Reputation: 11

Convert datetime to epoch time with seconds in SQL Server 2008

SELECT  
    CONVERT(BIGINT, DATEDIFF(ss, '1970-01-01 00:00:00', replogindatetime)) AS login,
    CONVERT(BIGINT, DATEDIFF(ss, '1970-01-01 00:00:00', replogoutdatetime)) AS logout
FROM 
    #Table

The RepLoginDateTime & RepLogoutDateTime are 2 columns in SQL Server datetime datatype, which I have to convert to epoch timestamp, but I get this error:

Msg 535, Level 16, State 0, Line 23
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.

I'm using SQL Server 2008

Upvotes: 0

Views: 7248

Answers (2)

Ross Bush
Ross Bush

Reputation: 15175

You have to go around the conversion functions and use a BIGINT instead.

DECLARE @Epoch DATETIME = CAST(0 as DATETIME)
DECLARE @TestDate DATETIME = GETDATE()

DECLARE @DaysPast BIGINT = (SELECT DATEDIFF(DAY, @Epoch,@TestDate))  

DECLARE @TimeOfDayInMilliseconds BIGINT = (SELECT  DATEDIFF(ms, 0, DATEADD(Day, 0 - DATEDIFF(Day, 0, @TestDate), @TestDate)))

SELECT (@DaysPast * 86400000) + @TimeOfDayInMilliseconds

Upvotes: 0

Thom A
Thom A

Reputation: 95564

DATEDIFF in SQL Server returns an int. From SQL Server 2016 you also have DATEDIFF_BIG, which returns a bigint. This means, if you have a value that is going to be too large for an int you'll get an overflow error when using DATEDIFF.

It, however, surprises me your getting an overflow here, as 1970-01-01 plus 2,147,483,647 seconds is 2038-01-19 03:14:07.

Anyway, instead, why not get the days, and then the seconds, and add:

SELECT CONVERT(BIGINT,DATEDIFF(DAY, '19700101','2038-10-12 12:30:49')) * 86400 +
       DATEDIFF(ss, DATEADD(DAY, DATEDIFF(DAY, 0, '2038-10-12 12:30:49'),0), '2038-10-12 12:30:49')

Upvotes: 1

Related Questions