如何在 SQL Server 2005 中转换时区? [英] How to convert timezones in SQL Server 2005?

问题描述

我所有的时间都在 UTC 时区，现在我需要以某种方式将其转换为用户时区(我也将其存储在数据库中并使用 Windows 时区的 ID).

All my times are in UTC timezone now I need to somehow convert it to the users timezone(I have it stored in the db as well and uses the ids of the windows timezones).

如何在 SQL Server 2005 中执行此操作?

How can I do this in SQL Server 2005?

编辑

所以我尝试执行扩展存储过程，但是使用 Timezoneinfo 时出现此错误

So I tried to do that extended stored procedure but with Timezoneinfo I get this error

部署错误 SQL01268:.Net SqlClient数据提供者:消息 6503，级别 16，州 12，1 号线组装'system.core，版本=3.5.0.0，文化=中立，publickeytoken=b77a5c561934e089.'曾是在 SQL 目录中找不到.一个批处理时发生错误正在执行.

Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6503, Level 16, State 12, Line 1 Assembly 'system.core, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089.' was not found in the SQL catalog. An error occurred while the batch was being executed.

如果我去掉那条线，我就可以部署它.任何想法如何解决这个问题?

if I take that line out I can deploy it. Any ideas how to get around this?

推荐答案

由于 SQL Server 没有为此提供开箱即用的支持，您可以考虑编写一个 .Net dll 存储过程，它利用了 .Net TimeZoneInfo 对象，该对象考虑了包括夏令时在内的所有规则.这个类也允许您将时间从一个区域转换到另一个区域.我希望这会有所帮助.

Since SQL Server doesn't provide out of the box support for this, you might consider writing a .Net dll stored procedure, that makes use of the .Net TimeZoneInfo object , this object takes all rules including DST into consideration. This class allows you to convert time from one zone to another too. I hope this helps.

DateTime hwTime = new DateTime(2007, 02, 01, 08, 00, 00);
try
{
   TimeZoneInfo hwZone = TimeZoneInfo.FindSystemTimeZoneById("Hawaiian Standard Time");
   Console.WriteLine("{0} {1} is {2} local time.", 
           hwTime, 
           hwZone.IsDaylightSavingTime(hwTime) ? hwZone.DaylightName : hwZone.StandardName, 
           TimeZoneInfo.ConvertTime(hwTime, hwZone, TimeZoneInfo.Local));
}
catch (TimeZoneNotFoundException)
{
   Console.WriteLine("The registry does not define the Hawaiian Standard Time zone.");
}                           
catch (InvalidTimeZoneException)
{
   Console.WriteLine("Registry data on the Hawaiian STandard Time zone has been corrupted.");
}

教程创建简单的 .Net DLL 存储过程.

另一个有用的教程，有关于部署的更多细节.

这篇关于如何在 SQL Server 2005 中转换时区?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！