UNIX_TIMESTAMP in SQL Server

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 integer
AS 
BEGIN
  /* Function body */
  declare @return integer

  SELECT @return = DATEDIFF(SECOND,{d '1970-01-01'}, @ctimestamp)

  return @return
END

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

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

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

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

-- MySQL
SELECT 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)