CODEWITHSUNDEEP
sql-serversql-server-2008datetimetimezone
Mokko Field
Mokko Field

Reputation: 33

convert datetime to UTC value mssql

I have the query like this:

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

How i can cast "table.b" to UTC time just adding CAST into the query?

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

I'm not able to cast it separately and declaring local variables.

Upvotes: 2

Views: 12372

Answers (2)

panpawel
panpawel

Reputation: 1848

I solved the problem by creating a temporary table that stores the date range and the current UTC offset. Here is the partial solution that works for US starting at year 2007, for regions that observe DST. It can be easily modified to for years before 2007. Please see the end of the solution for a sample usage

    -- 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

Upvotes: 1

Cristian Lupascu
Cristian Lupascu

Reputation: 40506

You can write your query as follows:

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

This converts the values in column bto UTC, by adding to those values the tmie difference that currently exists between the local date (getDate()) and the UTC date (getUtcDate()).

In order for the above query to work, the following must be true:

  • the date(s) stored in column b must be expressed in local time
  • the server date/time zone should be properly configured

Upvotes: 7

Related Questions