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())

from : https://www.stev.org/post/mssqlconvertunixtimestamptodatetime