IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[FUNC_UNIXCONVERT]‘) AND type IN (N’TF’,N’FN’ ) )
DROP FUNCTION [dbo].[FUNC_UNIXCONVERT];
GO
/****** 对象: Scalar Function [dbo].[FUNC_UNIXCONVERT] 脚本日期: 21/10/2022 3:03:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
–unix时间装时间 SELECT [dbo].FUNC_UNIXCONVERT
create function [dbo].[FUNC_UNIXCONVERT]
(
@ai_unix bigint --时间戳
)
RETURNS DATETIME2(7)
as
begin
if @ai_unix<=0
return null
--时间戳长度
declare @ai_len bigint=len(@ai_unix)
IF(@ai_len = 13)
BEGIN
-- MILLISECOND(毫秒)
return DATEADD(HH,-4,DATEADD(MS, @ai_unix % 1000, DATEADD(SECOND, @ai_unix / 1000, CAST('19700101' as datetime2(7)))))
END
else IF(@ai_len = 16)
BEGIN
-- MICROSECOND(微妙)
return DATEADD(HH,-4,DATEADD(MCS, @ai_unix % 1000000, DATEADD(SECOND, @ai_unix / 1000000, CAST('19700101' as datetime2(7)))))
END
else IF(@ai_len = 19)
BEGIN
-- NANOSECOND(纳秒)
return DATEADD(HH,-4,DATEADD(NS, @ai_unix % 1000000000, DATEADD(SECOND, @ai_unix / 1000000000, CAST('19700101' as datetime2(7)))))
END
else IF(@ai_len = 10)
BEGIN
--秒
return DATEADD(SECOND, @ai_unix , CAST('19700101' as datetime2(7)))
end
RETURN NULL
end