locked
How to Covert UTC time to Local RRS feed

  • Question

  • I have a table with DateTime Column, and the values are stored in UTC. I need create a view with column of local time.

    BTW, I don't think following scripts works correctly against existing rows, because it is not consider about the case of Summer time

    SELECT @offset = DATEDIFF(hh, GETUTCDATE(), GETDATE())
    SELECT UTCdate + @offset AS localdate FROM table

    Thanks!
    Sera
    • Edited by SeraYY Thursday, April 16, 2009 3:40 AM
    Thursday, April 16, 2009 3:29 AM

Answers

  • Following scripts is assuming the summer time start from the 2nd Sunday of March till the first Sunday of November.



    CREATE FUNCTION [dbo].[UTC2LocalDateTime]
    (
     -- Add the parameters for the function here
     @theUTCDateTime datetime
    )
    RETURNS DateTime
    AS
    BEGIN
     -- Declare the return variable here
     DECLARE @ResultDateTime datetime

     -- Varables
     DECLARE @SummerTimeStartDate date, -- the Second Sunday of March
       @SummerTimeEndDate date -- the first Sunday of November
     
     DECLARE @DateTime31 datetime, @Weekday31 int,  -- March 1st
       @Datetime111 datetime, @Weekday111 int -- November 1st
     
     SET @DateTime31 = cast(rtrim(Year(@theUTCDateTime)*10000+301) as datetime) 
     SET @Weekday31 = (DATEPART (weekday, @DateTime31 ) + @@DATEFIRST -1) % 7
     If @Weekday31 = 0
      SET @Weekday31 =7
      
     --the Second Sunday of March
     SET @SummerTimeStartDate=DATEADD(day,7+7-@Weekday31,@DateTime31)

     SET @Datetime111 = cast(rtrim(Year(@theUTCDateTime)*10000+1101) as datetime) 
     SET @Weekday111 = (DATEPART ( weekday, @Datetime111 )+ @@DATEFIRST -1) % 7
     If @Weekday111 = 0
      SET @Weekday111 = 7
     -- the first Sunday of November
     SET @SummerTimeEndDate=DATEADD(day,7-@Weekday111,@Datetime111)
     
      
     
     IF @theUTCDateTime <= @SummerTimeStartDate or @theUTCDateTime > @SummerTimeEndDate -- Winter time
     Begin
      SET @ResultDateTime= DATEADD(hh,-8,@theUTCDateTime) 
     End
     Else -- Summber time
     Begin
      SET @ResultDateTime= DATEADD(hh,-7,@theUTCDateTime) 
     End 
     
     -- Return the result of the function
     RETURN @ResultDateTime

    END


    Jeffrey
    • Proposed as answer by Jeffrey Zeng - MSFT Thursday, April 16, 2009 11:02 AM
    • Marked as answer by SeraYY Thursday, April 16, 2009 1:15 PM
    Thursday, April 16, 2009 11:02 AM

All replies

  • I don't know if there's a more direct way, but it seems like something like what you posted makes sense:

    SELECT DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),Table.UTCDateColumn) AS LocalDate
    FROM Table


    --Brad
    Thursday, April 16, 2009 3:37 AM
  • I don't know if there's a more direct way, but it seems like something like what you posted makes sense:

    SELECT DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),Table.UTCDateColumn) AS LocalDate
    FROM Table


    --Brad
    Sorry for confusion. my question is covert UTC to Localtime against existing data, that is why I don't think DATEADD\DATEDIFF works

    Sera
    Thursday, April 16, 2009 3:42 AM
  • Sera's point make sense. It doesn't count in the summer time against existing data in the script of "DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),Table.UTCDateColumn)"
    Jeffrey
    Thursday, April 16, 2009 3:50 AM
  • For you reference, Assuming the summer time start from the Second Sunday of March till the first Sunday of November CREATE FUNCTION [Fact].[UTC2LocalDateTime] ( -- Add the parameters for the function here @theUTCDateTime datetime ) RETURNS DateTime AS BEGIN -- Declare the return variable here DECLARE @ResultDateTime datetime -- Varables DECLARE @SummerTimeStartDate date, -- the Second Sunday of March @SummerTimeEndDate date -- the first Sunday of November DECLARE @DateTime31 datetime, @Weekday31 int, -- March 1st @Datetime111 datetime, @Weekday111 int -- November 1st SET @DateTime31 = cast(rtrim(Year(@theUTCDateTime)*10000+301) as datetime) SET @Weekday31 = (DATEPART (weekday, @DateTime31 ) + @@DATEFIRST -1) % 7 If @Weekday31 = 0 SET @Weekday31 =7 --the Second Sunday of March SET @SummerTimeStartDate=DATEADD(day,7+7-@Weekday31,@DateTime31) SET @Datetime111 = cast(rtrim(Year(@theUTCDateTime)*10000+1101) as datetime) SET @Weekday111 = (DATEPART ( weekday, @Datetime111 )+ @@DATEFIRST -1) % 7 If @Weekday111 = 0 SET @Weekday111 = 7 -- the first Sunday of November SET @SummerTimeEndDate=DATEADD(day,7-@Weekday111,@Datetime111) IF @theUTCDateTime <= @SummerTimeStartDate or @theUTCDateTime > @SummerTimeEndDate -- Winter time Begin SET @ResultDateTime= DATEADD(hh,-8,@theUTCDateTime) End Else -- Summber time Begin SET @ResultDateTime= DATEADD(hh,-7,@theUTCDateTime) End -- Return the result of the function RETURN @ResultDateTime END
    Jeffrey
    Thursday, April 16, 2009 10:57 AM
  • Following scripts is assuming the summer time start from the 2nd Sunday of March till the first Sunday of November.



    CREATE FUNCTION [dbo].[UTC2LocalDateTime]
    (
     -- Add the parameters for the function here
     @theUTCDateTime datetime
    )
    RETURNS DateTime
    AS
    BEGIN
     -- Declare the return variable here
     DECLARE @ResultDateTime datetime

     -- Varables
     DECLARE @SummerTimeStartDate date, -- the Second Sunday of March
       @SummerTimeEndDate date -- the first Sunday of November
     
     DECLARE @DateTime31 datetime, @Weekday31 int,  -- March 1st
       @Datetime111 datetime, @Weekday111 int -- November 1st
     
     SET @DateTime31 = cast(rtrim(Year(@theUTCDateTime)*10000+301) as datetime) 
     SET @Weekday31 = (DATEPART (weekday, @DateTime31 ) + @@DATEFIRST -1) % 7
     If @Weekday31 = 0
      SET @Weekday31 =7
      
     --the Second Sunday of March
     SET @SummerTimeStartDate=DATEADD(day,7+7-@Weekday31,@DateTime31)

     SET @Datetime111 = cast(rtrim(Year(@theUTCDateTime)*10000+1101) as datetime) 
     SET @Weekday111 = (DATEPART ( weekday, @Datetime111 )+ @@DATEFIRST -1) % 7
     If @Weekday111 = 0
      SET @Weekday111 = 7
     -- the first Sunday of November
     SET @SummerTimeEndDate=DATEADD(day,7-@Weekday111,@Datetime111)
     
      
     
     IF @theUTCDateTime <= @SummerTimeStartDate or @theUTCDateTime > @SummerTimeEndDate -- Winter time
     Begin
      SET @ResultDateTime= DATEADD(hh,-8,@theUTCDateTime) 
     End
     Else -- Summber time
     Begin
      SET @ResultDateTime= DATEADD(hh,-7,@theUTCDateTime) 
     End 
     
     -- Return the result of the function
     RETURN @ResultDateTime

    END


    Jeffrey
    • Proposed as answer by Jeffrey Zeng - MSFT Thursday, April 16, 2009 11:02 AM
    • Marked as answer by SeraYY Thursday, April 16, 2009 1:15 PM
    Thursday, April 16, 2009 11:02 AM