访问的TimeZoneInfo从SQL 2005服务器 [英] Access TimeZoneInfo from SQL 2005 Server

问题描述

在.NET的TimeZoneInfo类是伟大的，我认为它会回答我与来自多个时区的记录数据的所有问题在我的SQL 2005数据库。

The .NET TimeZoneInfo class is great and I thought it would answer all my issues with recording data from multiple time zones in my SQL 2005 database.

要在数据库中转换UTC日期时间，以任何其它时区我会用TimeZoneInfo.FindSystemTimeZoneById（）刚刚得到的时区成的TimeZoneInfo类，然后调用TimeZoneInfo.ConvertTimeFromUtc（）。辉煌！我只是把这个从SQL .NET CLR！

To convert a UTC datetime in the database to any other time zone i'd just get the time zone into a TimeZoneInfo class using TimeZoneInfo.FindSystemTimeZoneById() and then call the TimeZoneInfo.ConvertTimeFromUtc(). Brilliant! I'd just call this from the SQL .NET CLR!

但是...的TimeZoneInfo有MayLeakOnAbort的主机保护属性。

BUT...TimeZoneInfo has a Host Protection Attribute of MayLeakOnAbort.

当我使用VS 2008来创建一个SQL函数或存储过程，我甚至不能看到system.TimeZoneInfo类没关系使用它。我假设也是即使我能以某种方式引用的TimeZoneInfo类的，我可能会得到某种安全异常，如果我试图在SQL Sever的2005年注册程序集。

When I use VS 2008 to create an SQL function or stored procedure, I cannot even see the system.TimeZoneInfo class nevermind use it. I'm assuming also that even if I could somehow reference the TimeZoneInfo class, I would probably get some sort of security exception if I tried to register the assembly in SQL Sever 2005.

帮助！有什么办法来访问SQL Server 2005的？

Help! Is there any way to access TimeZoneInfo class and all its riches from SQL Server 2005?

注：我刚刚加入这个警告的第一个答案后：

NB: I've just added this caveat after the first Answer:

我们已经在全球不同地点的网站。我们需要存储本地时间和UTC时间针对其可能需要在网站级别趋势的事件数据库。一个趋势可能在一年由超过52,000个数据点，因此，为了提高效率，我不能只保存时间在DB UTC和转换数据点的每个客户端上。因此，我需要的能力，数据库内本地时间任何时区转换，并从UTC时间。

We have sites at different locations around the world. We need to store local time and UTC time in the database against events which may require trending at Site level. A trend may consist of over 52,000 data points over a year, so, for efficiency, I cannot just store times in UTC in the DB and convert every datapoint on the client. Thus I need the ability, within the DB to convert a local time in any timezone to and from UTC time.

推荐答案

我完成了一个SQL 2008数据库上这样做。

I just finished doing this on a SQL 2008 database.

首先我必须设置为DB守信，验证所有者是正确的。

First I had to set the DB to trustworthy and verify the owner was correct.

use [myDB]
go
alter database [myDB] set trustworthy on
go

exec sp_changedbowner 'sa'
go

接下来，我创建了一个.NET解决方案

Next, I created a .NET solution

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Collections.ObjectModel
Imports System.Runtime.InteropServices

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub sp_ConvertTime(ByVal UTCTime As DateTime, ByVal ZoneID As String, <Out()> ByRef Output As DateTime)
    Dim sp As SqlPipe = SqlContext.Pipe

    Dim ConvertedTime As DateTime
    Dim tzUTC = TimeZoneInfo.FindSystemTimeZoneById("UTC")
    Dim tzNew = TimeZoneInfo.FindSystemTimeZoneById(ZoneID)

    ConvertedTime = TimeZoneInfo.ConvertTime(UTCTime, tzUTC, tzNew)

    Output = ConvertedTime
    sp.Send(ConvertedTime)

    ConvertedTime = Nothing
    tzUTC = Nothing
    tzNew = Nothing
    sp = Nothing

End Sub
End Class

部署之前我设置的权限级别为不安全。

接下来，我部署了出来我检查编译错误输出窗口并纠正这些。

Next I deployed it out I checked the Output window for Build errors and corrected those.

下面是SQL测试

DECLARE @UTCTime datetime
DECLARE @ZoneID varchar(21)
DECLARE @NewTime datetime

SET @UTCTime = GETUTCDATE()
SET @ZoneID = 'Central Standard Time'

exec sp_ConvertTime @UTCTime, @ZoneID, @NewTime OUTPUT
select @NewTime AS NewTime

这篇关于访问的TimeZoneInfo从SQL 2005服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！