MSSQL Unix timestamp conversion
So here is a quick function that turns a unix timestamp into a datetime and to convert a datetime to a unix timestamp. This is a reasonably easy task to perform with the builtin mssql date functions. Since a unix timestamp is the number of second since the 1st January 1970 we simply add these together.
To get a unix timestamp works in exactly the same way in reverse we simply find the number of seconds since 1st January 1970 using datediff. Its best to use these builtin functions because they will take care of the extra days in leap years for us.
CREATE FUNCTION [dbo].[UNIXToDateTime] (@timestamp int) RETURNS datetime AS BEGIN DECLARE @ret datetime SELECT @ret = DATEADD(second, @timestamp, '1970/01/01 00:00:00') RETURN @ret END GO CREATE FUNCTION [dbo].[DateTimeToUNIX] (@date datetime) RETURNS int AS BEGIN DECLARE @ret int SELECT @ret = DATEDIFF(second, '1970/01/01 00:00:00', @date) RETURN @ret END GO
An example on how to use the functions above
SELECT dbo.UNIXToDateTime(dbo.DateTimeToUNIX(GETDATE())) SELECT dbo.DateTimeToUNIX(GETDATE())
Did You find this page useful?
Thanks for the feeback. Please consider sharing with others.