How do I convert a SQL Server DATETIME value to a Unix timestamp?

Many people have asked how to convert a DATETIME value in SQL Server to a more familiar Unix-style timestamp. Well, the timestamp is merely an offset, in seconds, from Jan 1, 1970, at midnight. Therefore: 
 

SELECT DATEDIFF(s, '19700101', GETDATE())  -- or  SELECT timestamp = DATEDIFF(s, '19700101', someColumn)      FROM someTable

 
Now, there is one caveat with this method. It will only work on dates prior to 2038-01-19 at 3:14:08 AM, where the delta in seconds exceeds the limit of the INT datatype (INT is used as the result of DATEDIFF). If you want your code to handle dates beyond 2038-01-18, and you are running SQL Server 2000, you can write a user-defined function: 
 

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

 
The Other Way 
 
Also, some people have asked how to get the datetime value when all they have is a Unix timestamp. Similar to above, only you use DATEADD. Let's say your timestamp value is 1067442089, then to determine what point in time that represents, you would run: 
 

SELECT DATEADD(s, 1067441023, '19700101')

 
Results: 
 

-----------------------  2003-10-29 15:23:43.000

 
(Of course you could also do this against a column in a table, or a variable, etc.)