如何从tsql（sql 2005）中的utc datetime计算本地datetime？ [英] How to calculate the local datetime from a utc datetime in tsql (sql 2005)?

问题描述

我想在tsql中循环一段时间，并打印utc datetimes和我们的本地变体。
我们住在UTC +1，所以我可以轻松添加1小时，但在夏季，我们住在UTC +2。

i want to loop over a period of time in tsql, and print the utc datetimes and our local variant. We live in UTC +1, so i could easily add 1 hour, but in the summertime we live in UTC +2.

在C＃中，我可以创建一个日期时间，并使用一种方法来请求UTC变体，反之亦然。

In C# i can create a datetime and use a method to ask for the UTC variant and vice versa.

现在我已经有了：

declare @counter int
declare @localdate datetime
declare @utcdate datetime
 set @counter = 0
 while @counter < 100
 begin
   set @counter = @counter + 1
   print 'The counter is ' + cast(@counter as char)
  set @utcdate  = DATEADD(day,@counter,GETUTCDATE())
  --set @localdate = ????

  print  @localdate  
  print @utcdate
 end

推荐答案

假设您正在使用SQL 2005，您可以开发一个SQL CLR函数来采用UTC日期并转换为本地日期。

Assuming you are using SQL 2005 upwards, you can develop a SQL CLR function to take a UTC date and convert to the local date.

此链接是MSDN如何解释如何在C＃中创建标量UDF。

This link is an MSDN How-To explaining how you can create a scalar UDF in C#.

沿

[SqlFunction()]
public static SqlDateTime ConvertUtcToLocal(SqlDateTime utcDate) 
{
    // over to you to convert SqlDateTime to DateTime, specify Kind
    // as UTC, convert to local time, and convert back to SqlDateTime
}

以上您的示例将变为

set @localdate = dbo.ConvertUtcToLocal(@utcdate)

SQL CLR在部署方面有其开销，但我觉得这样的情况是最适合的。

SQL CLR has its overheads in terms of deployment, but I feel cases like this are where it fits in best.

这篇关于如何从tsql（sql 2005）中的utc datetime计算本地datetime？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！