UNIX_TIMESTAMP in SQL Server

If you're not bothered about dates before 1970, or millisecond precision, just do:

-- SQL ServerSELECT DATEDIFF(s, '1970-01-01 00:00:00', DateField)

Almost as simple as MySQL's built-in function:

-- MySQLSELECT UNIX_TIMESTAMP(DateField);

Other languages (Oracle, PostgreSQL, etc): How to get the current epoch time in ...

If you need millisecond precision (SQL Server 2016/13.x and later):

SELECT DATEDIFF_BIG(ms, '1970-01-01 00:00:00', DateField)

Try this post:https://web.archive.org/web/20141216081938/http://skinn3r.wordpress.com/2009/01/26/t-sql-datetime-to-unix-timestamp/

CREATE FUNCTION UNIX_TIMESTAMP (@ctimestamp datetime)RETURNS integerAS BEGIN  /* Function body */  declare @return integer  SELECT @return = DATEDIFF(SECOND,{d '1970-01-01'}, @ctimestamp)  return @returnEND

or this post:

http://mysql.databases.aspfaq.com/how-do-i-convert-a-sql-server-datetime-value-to-a-unix-timestamp.html

code is as follows:

CREATE FUNCTION dbo.DTtoUnixTS (     @dt DATETIME ) RETURNS BIGINT AS BEGIN     DECLARE @diff BIGINT     IF @dt >= '20380119'     BEGIN         SET @diff = CONVERT(BIGINT, DATEDIFF(S, '19700101', '20380119'))             + CONVERT(BIGINT, DATEDIFF(S, '20380119', @dt))     END     ELSE         SET @diff = DATEDIFF(S, '19700101', @dt)     RETURN @diff END

Sample usage:

SELECT dbo.DTtoUnixTS(GETDATE()) -- or SELECT UnixTimestamp = dbo.DTtoUnixTS(someColumn)     FROM someTable

Sql Server 2016 and later have a DATEDIFF_BIG function that can be used to get the milliseconds.

SELECT DATEDIFF_BIG(millisecond, '1970-01-01 00:00:00', GETUTCDATE())

Create a function

CREATE FUNCTION UNIX_TIMESTAMP()    RETURNS BIGINTASBEGIN    RETURN DATEDIFF_BIG(millisecond, '1970-01-01 00:00:00', GETUTCDATE())END

And execute it

SELECT dbo.UNIX_TIMESTAMP()