在 SQL Server 中将 UTC 毫秒转换为 DATETIME [英] Convert UTC Milliseconds to DATETIME in SQL server

问题描述

我想在 SQL Server 中将 UTC 毫秒转换为 DateTime.

I want to convert UTC milliseconds to DateTime in SQL server.

这可以通过以下代码在 C# 中轻松完成:

This can easily be done in C# by following code:

DateTime startDate = new DateTime(1970, 1, 1).AddMilliseconds(1348203320000);

我需要在 SQL 服务器中执行此操作.我在这里找到了一些脚本，但是这是从 1900-01-01 开始的初始滴答声.

I need to do this in SQL server. I found some script here, but this was taking initial ticks from 1900-01-01.

我已经使用了 DATEADD 函数，如下所示，但这是通过支持毫秒作为差异给出了算术溢出异常:

I have used the DATEADD function as below, but this was giving an arithmetic overflow exception by supping milliseconds as difference:

SELECT DATEADD(MILLISECOND,1348203320000,'1970-1-1')

如何正确进行转换?

推荐答案

DECLARE @UTC BIGINT
SET @UTC = 1348203320997 

SELECT DATEADD(MILLISECOND, @UTC % 1000, DATEADD(SECOND, @UTC / 1000, '19700101'))

这篇关于在 SQL Server 中将 UTC 毫秒转换为 DATETIME的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！