SQL Server在另一个时区中指定时间

SQL Server在另一个时区中指定时间

由于夏令时可能会影响英国时区，因此建议您建立一个DST表，并使用该表创建日期。

此解决方案引用了本文：http :
//www.mssqltips.com/sqlservertip/3173/handle-conversion-between-time-zones-
in-sql-server–part-1/

创建表：

CREATE TABLE dbo.TZCalendar(  [Year]        Int PRIMARY KEY,  UTC_DST_Start SMALLDATETIME NOT NULL,  UTC_DST_End   SMALLDATETIME NOT NULL,  ET_DST_Start  AS ConVERT(SMALLDATETIME,DATEADD(HOUR, -4, UTC_DST_Start)),  ET_DST_End    AS ConVERT(SMALLDATETIME,DATEADD(HOUR, -5, UTC_DST_End)));SET DATEFIRST 7;;WITH cte(d,p) AS (  -- all the years from 2000 through 50 years after the current year:  SELECt TOP (YEAR(GETDATE())-2000+51) DATEADD(YEAR,number,'20000101'),    CASE WHEN number < 7 THEN 1 ELSE 0 END -- year < 2007 = 1, else 0    FROM [master].dbo.spt_values WHERe [type] = N'P' ORDER BY number)INSERT dbo.TZCalendar([Year],UTC_DST_Start,UTC_DST_End)SELECt Year(d), -- First Sunday in April (< 2007) or second Sunday in March (>= 2007): DATEADD(HOUR, 7, DATEADD(DAY,(7-DATEPART(WEEKDAY,DATEADD(MONTH,2+p,d))+1)%7    +(7*ABS(p-1)),DATEADD(MONTH,2+p,d))), -- Last Sunday in October (< 2007) or first Sunday in November (>= 2007): DATEADD(HOUR, 6, DATEADD(DAY,(7-DATEPART(WEEKDAY,DATEADD(MONTH,10,d))+1)%7    -(7*p),DATEADD(MONTH,10,d)))FROM cteORDER BY d;

接下来，我将创建一个函数来提取当前的英国时间：

Create Function dbo.fnGetUKTime()Returns DateTimeAs Begin    Declare @London DateTime = GetUTCDate()    Declare @Offset Int = 0    Select  @Offset = Case When @London Between UTC_DST_Start And UTC_DST_End Then 1 Else 0 End    From    dbo.TZCalendar    Where   Year = Year(@London)    Set @London = DateAdd(Hour, @Offset, @London)    Return @LondonEndGo

然后，您可以在查询中引用它：

select *from tablewhere blah blahand DatePart(Hour, dbo.fnGetUKTime()) > 14