我需要在SQL Server2008中创建一个模拟mysql的UNIX_TIMESTAMP()
的函数。
提前感谢!
发布于 2016-01-13 19:40:56
如果你不关心1970年之前的日期,或者毫秒精度,那就这么做吧:
-- SQL Server
SELECT DATEDIFF(s, '1970-01-01 00:00:00', DateField)
几乎和MySQL的内置函数一样简单:
-- MySQL
SELECT UNIX_TIMESTAMP(DateField);
其他语言(Oracle、PostgreSQL等):How to get the current epoch time in ...
如果需要毫秒精度(SQL Server 2016/13.x及更高版本):
SELECT DATEDIFF_BIG(ms, '1970-01-01 00:00:00', DateField)
发布于 2012-01-12 23:07:52
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
或者这篇文章:
代码如下:
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
发布于 2019-04-03 04:36:28
Sql Server2016及更高版本有一个DATEDIFF_BIG函数,可用于获取毫秒数。
SELECT DATEDIFF_BIG(millisecond, '1970-01-01 00:00:00', GETUTCDATE())
创建函数
CREATE FUNCTION UNIX_TIMESTAMP()
RETURNS BIGINT
AS
BEGIN
RETURN DATEDIFF_BIG(millisecond, '1970-01-01 00:00:00', GETUTCDATE())
END
并执行它
SELECT dbo.UNIX_TIMESTAMP()
https://stackoverflow.com/questions/8837225
复制相似问题