sql - SQL Server中的UNIX_TIMESTAMP

Question

我需要在SQL Server 2008中创建一个模仿mysql的UNIX_TIMESTAMP()的函数。

提前致谢 !

Answer 1

试试这个帖子:
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

或这篇文章:

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

代码如下:

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

用法示例:

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