Hello. I need to convert this input string 11/20/2015 7:00 PM GMT +0:00 to an Epoch time format and unsure how to do this? When I try something like SELECT DATEDIFF(s, '1970-01-01 00:00:00', '11/20/2015 7:00 PM GMT +0:00') it does not work and I assume that is because 11/20/2015 7:00 PM GMT +0:00 is not in the proper format for the statement. If I use the
epochconverter.com Human Date to Timestamp converter it works so I know it is possible. I assume some other functions like Java script etc are at play though. I need strictly SQL conversions though.
First, I'll admit I had to look up Epoch Time via the link you provided. For anyone else who doesn't know ... The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds (in ISO 8601: 1970-01-01T00:00:00Z). Literally speaking the epoch is Unix time 0 (midnight 1/1/1970), but 'epoch' is often used as a synonym for 'Unix time'. Many Unix systems store epoch dates as a signed 32-bit integer, which might cause problems on January 19, 2038 (known as the Year 2038 problem or Y2038). They actually do provide a SQL Server solution on that site: SELECT DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE()) The GETUTCDATE() function returns the current UTC date/time on the server. To use a different date (or string), we first want to ensure we have a datetime2 value. If all of your date/time values are GMT expressed as that example, the following should work: DECLARE @startdate datetimeoffset(0) = CONVERT(datetimeoffset(0), '19700101', 112); DECLARE @datestring varchar(50) = '11/20/2015 7:00 PM GMT +0:00'; DECLARE @datevalue datetimeoffset(0) = CONVERT(datetimeoffset(0), REPLACE(@datestring, ' GMT', '')); SELECT DATEDIFF(second, @startdate, @datevalue); --------------- UPDATE: The short answer to your question about an end date 4 hours later is to just add (4*3600) to the start time because Epoch time is a count of seconds. That said, I decided to create a table-valued function to return the start and end Epoch times given a specific time and the number of hours until the end. I'll show you the function and then give an example of how to use it. CREATE FUNCTION dbo.GetEpochStartAndEnd ( @StartDateString varchar(50) , @HoursUntilEnd int ) RETURNS TABLE AS RETURN ( SELECT StartEpochTime = DATEDIFF ( second , CONVERT(datetimeoffset(0), '19700101', 112) , CONVERT(datetimeoffset(0), REPLACE(@StartDateString, ' GMT', '')) ) , EndEpochTime = DATEDIFF ( second , CONVERT(datetimeoffset(0), '19700101', 112) , CONVERT(datetimeoffset(0), REPLACE(@StartDateString, ' GMT', '')) ) + @HoursUntilEnd * 3600 ); Here is an example of how you could use this: SELECT o.modify_date, e.StartEpochTime, e.EndEpochTime FROM sys.objects o CROSS APPLY dbo.GetEpochStartAndEnd(CONVERT(varchar(50), o.modify_date, 121), 4) e ;