So here is a quick function that turns a unix timestamp into a datetime and to convert a datetime to a unix timestamp

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 it

SELECT dbo.UNIXToDateTime(dbo.DateTimeToUNIX(GETDATE()))


SELECT dbo.DateTimeToUNIX(GETDATE())




Last Modified: 30 November 2016

Releated Posts


2012-08-07 - MSSQL - Finding the database restore history
2011-09-24 - MSSQL - Checking Uptime
2011-08-11 - MSSQL - Saving memory by using stored procedures
2011-08-09 - MSSQL - Last Backup time and size
2011-06-24 - MSSQL - Extracting part of an email address
2011-06-15 - MSSQL - Bulk Deleting rows with backup
2011-06-07 - MSSQL - DBCC Check All Databases
2011-06-03 - MSSQL - Who locks what
2011-05-26 - MSSQL - RandomString Function
2011-05-22 - MSSQL - Removing the aspnet membership database
2011-05-19 - MSSQL - ShrinkLog
2011-05-16 - MSSQL - Enum
2011-05-05 - MSSQL - Log Sizes
2011-03-01 - MSSQL - Kill connections by host
2011-02-23 - C Sharp / MSSQL Get inserted value of NEWSEQUENTIALID()
2011-02-22 - MSSQL - Kill connections by username
2011-02-20 - MSSQL - All temp tables are global
2011-02-20 - MSSQL - Unsigned int
2011-02-17 - MSSQL - Convert IP To big int
2011-02-05 - MSSQL - Levenshtein
2011-02-02 - MSSQL - Kill connections to a database
2011-02-01 - MSSQL - Convert unix timestamp to date time
2011-01-30 - MSSQL - Enabling CLR Functions
2011-01-30 - MSSQL - RegEx Support
2011-01-30 - MSSQL - Adding MD5 checksum support
2011-01-30 - MSSQL - Adding SHASum support
2011-01-29 - MSSQL - TRIM Function