//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   Alerts
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
mysql.databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: MySQL :: How do I convert a SQL Server DATETIME value to a Unix timestamp?


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

 

 


Created: 2/19/2003 | Last Updated: 10/29/2003 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (179)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...