如何将日期时间转换为MS SQL存储函数中的时间戳 [英] How to convert datetime to timestamp in MS SQL stored function

问题描述

有一个从表更新触发器调用的存储函数。像这样的Smt：

There is a stored function which is called from table update trigger. Smt like this:

FUNCTION [dbo].[DateTime2ToBigInt](@dt DATETIME2(7))
RETURNS BIGINT

需要将输入日期时间转换为unix时间戳。

Need to convert input datetime to unix timestamp.

尝试 CONVERT（timestamp，@dt）和 CAST（@dt AS TIMESTAMP） ，但两者都会导致

Tried CONVERT( timestamp, @dt) and CAST(@dt AS TIMESTAMP) but both result in

不允许从
到数据类型datetime2的显式转换。

"Explicit conversion from data type datetime2 to timestamp is not allowed."

当然可以用数学方法完成，但我不敢相信，mssql没有直接转换功能

Of course it's possible to do by mathematics, but I can't believe, that mssql doesn't have direct convert function

推荐答案

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

尝试使用该函数：

SELECT UNIX_TIMESTAMP(GETDATE());

这篇关于如何将日期时间转换为MS SQL存储函数中的时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！