将日期时间转换为UTC值mssql

Question

我有这样的查询：

SELECT table.a, table.b, table.c from Table table

如何将"table.b"转换为UTC时间，只需在查询中添加cast？

SELECT 
  table.a, 
  **%CAST_TO_UTC_FUNCTION to table.b%**, 
  table.c 
from Table table

我不能单独进行强制转换并声明局部变量。

Answer 1

您可以按如下方式编写查询：

SELECT 
  table.a, 
  dateAdd(
    second,
    dateDiff(second, getDate(), getUtcDate()),
    table.b) as b_converted_to_UTC,
  table.c 
from Table table

这会将列b中的值转换为UTC，方法是将当前存在于本地日期（getDate()）和UTC日期（getUtcDate()）之间的tmie差添加到这些值中。

为了使上述查询工作，必须满足以下条件：

• 存储在b列中的日期必须用当地时间表示
• 应正确配置服务器日期/时区

Answer 2

我通过创建一个临时表来解决这个问题，该表存储日期范围和当前UTC偏移量。以下是从2007年开始适用于美国的部分解决方案，适用于观测夏令时的地区。它可以很容易地在2007年之前的几年内修改为。请参阅解决方案末尾的示例用法

    -- GET UTC for time in US for region that observe Daylight Saving Time (meaning will not work for part of Arizona and Hawaii)
DECLARE 
    @DSTStartDay datetime = '2007-03-1 02:00:00', 
    @DSTEndDay datetime = '2007-11-1 02:00:00',  
    @i int = 0, 
    @currDate datetime, 
    @offset int
DECLARE    
    @DST TABLE (StartDate datetime, EndDate datetime, DSTOffset int)
-- Insert into @DST DSTOffset of 0 if not DST date range, and -1 if DST date as temporary offset value
-- Then when we get Time Zone Offset, we can update DSTOffset to actual current offset (hours)        
WHILE @i < 20 
BEGIN
    INSERT @DST     
    SELECT DATEADD(d,
                   15 - CASE DATEPART(dw, @DSTStartDay) 
                        WHEN 1 THEN 8
                        ELSE DATEPART(dw, @DSTStartDay)
                        END,
                   @DSTStartDay),
           DATEADD(d,
                   8 - CASE  DATEPART(dw, @DSTEndDay) 
                       WHEN 1 THEN 8
                       ELSE  DATEPART(dw, @DSTEndDay)
                       END,
                       @DSTEndDay),
           -1;
       SET @DSTStartDay = DATEADD(yy,1,@DSTStartDay)    
    INSERT @DST     
    SELECT DATEADD(d, 
                   8 - CASE  DATEPART(dw, @DSTEndDay) 
                       WHEN 1 THEN 8
                       ELSE  DATEPART(dw, @DSTEndDay)
                       END,
                   @DSTEndDay),
           DATEADD(d,
                   15 - CASE  DATEPART(dw, @DSTStartDay) 
                        WHEN 1 THEN 8
                        ELSE  DATEPART(dw, @DSTStartDay)
                        END,
                    @DSTStartDay), 
           0;        
   SET @DSTEndDay = DATEADD(yy,1,@DSTEndDay)    
   SET @i = @i + 1
END
-- Get temporary offset for current date
SET @currDate = GETDATE()
SELECT @Offset = DSTOffset FROM @DST 
WHERE StartDate < @currDate AND EndDate >= @currDate
-- Calculate Time Zone Offset (ignore DST) and update DSTOffset in @DST table from temporary  to actual offset
SET @Offset = DATEDIFF(hh, GETDATE(), GETUTCDATE()) - @Offset
UPDATE @DST
SET DSTOffset = DSTOffset + @Offset
--SELECT * FROM @DST - look at the table
--Sample usage
DECLARE @myDateNoDST datetime = '2014-03-08 06:00',
        @myDateWithDST datetime = '2014-03-09 06:00'
SELECT @myDateNoDST LocalDateNoDST,  
       (SELECT DATEADD(hh,DSTOffset,@myDateNoDST) FROM @DST d WHERE StartDate < @myDateNoDST AND EndDate >= @myDateNoDST) UTCDateNoDST,
        @myDateWithDST LocalDateWithDST,  
       (SELECT DATEADD(hh,DSTOffset,@myDateWithDST) FROM @DST d WHERE StartDate < @myDateWithDST AND EndDate >= @myDateWithDST) UTCDateWithDST