How to calculate the local datetime from a utc datetime in tsql (sql 2005)?

user_103089 0
Viewed 42729

i want to loop over a period of time in tsql, and print the utc datetimes and our local variant. We live in UTC +1, so i could easily add 1 hour, but in the summertime we live in UTC +2.

In C# i can create a datetime and use a method to ask for the UTC variant and vice versa.

Till now i have this:

declare @counter int
declare @localdate datetime
declare @utcdate datetime
 set @counter = 0
 while @counter < 100
 begin
   set @counter = @counter + 1
   print 'The counter is ' + cast(@counter as char)
  set @utcdate  = DATEADD(day,@counter,GETUTCDATE())
  --set @localdate = ????

  print  @localdate  
  print @utcdate
 end
timezone datetime tsql sql-server sql
10 Answers

This solution seems too obvious.

If you can get UTC Date with GETUTCDATE() and you can get your local date with GETDATE() you have an offset that you can apply for any datetime

SELECT DATEADD(hh, DATEPART(hh, GETDATE() - GETUTCDATE()) - 24, GETUTCDATE())

this should return the local time you executed the query,

SELECT DATEADD(hh, DATEPART(hh, GETDATE() - GETUTCDATE()) - 24, N'1/14/2011 7:00:00'  )

this will return 2011-01-14 02:00:00.000 because i'm in UTC +5

Unless I'm missing something?

user_2305850

While the question's title mentions SQL Server 2005, the question is tagged with SQL Server in general. For SQL Server 2016 and later, you can use:

SELECT yourUtcDateTime AT TIME ZONE 'Mountain Standard Time'

A list of time zones is available with SELECT * FROM sys.time_zone_info

user_1618160

For those stuck in SQL Server 2005 and don't want or can't use a udf - and particularly does outside of the USA - I've taken @Bobman's approach and generalized it. The following will work in the USA, Europe, New Zealand and Australia, with the caveat that not all Australian states observe DST, even states that are in the same "base" timezone. It's also easy to add DST-rules that aren't yet supported, just add a line to the @calculation values.

-- =============================================
-- Author:      Herman Scheele
-- Create date: 20-08-2016
-- Description: Convert UTC datetime to local datetime
--              based on server time-distance from utc.
-- =============================================
create function dbo.UTCToLocalDatetime(@UTCDatetime datetime)
returns datetime as begin
    declare @LocalDatetime datetime, @DSTstart datetime, @DSTend datetime

    declare @calculation table (
        frm smallint,
        til smallint,
        since smallint,
        firstPossibleStart datetime,-- Put both of these with local non-DST time!
          firstPossibleEnd datetime -- (In Europe we turn back the clock from 3 AM to 2 AM, which means it happens 2 AM non-DST time)
    )

    insert into @calculation
    values
        (-9, -2, 1967, '1900-04-24 02:00', '1900-10-25 01:00'), -- USA first DST implementation
        (-9, -2, 1987, '1900-04-01 02:00', '1900-10-25 01:00'), -- USA first DST extension
        (-9, -2, 2007, '1900-03-08 02:00', '1900-11-01 01:00'), -- USA second DST extension
        (-1,  3, 1900, '1900-03-25 02:00', '1900-10-25 02:00'), -- Europe
        (9.5,11, 1971, '1900-10-01 02:00', '1900-04-01 02:00'), -- Australia (not all Aus states in this time-zone have DST though)
        (12, 13, 1974, '1900-09-24 02:00', '1900-04-01 02:00')  -- New Zealand

    select top 1    -- Determine if it is DST /right here, right now/ (regardless of input datetime)
        @DSTstart = dateadd(year, datepart(year, getdate())-1900, firstPossibleStart),          -- Grab first possible Start and End of DST period
        @DSTend   = dateadd(year, datepart(year, getdate())-1900, firstPossibleEnd),            
        @DSTstart = dateadd(day, 6 - (datepart(dw, @DSTstart) + @@datefirst - 2) % 7, @DSTstart),-- Shift Start and End of DST to first sunday
        @DSTend   = dateadd(day, 6 - (datepart(dw, @DSTend) + @@datefirst - 2) % 7, @DSTend),
        @LocalDatetime = dateadd(hour, datediff(hour, getutcdate(), getdate()), @UTCDatetime),  -- Add hours to current input datetime (including possible DST hour)
        @LocalDatetime = case
                when frm < til and getdate() >= @DSTstart and getdate() < @DSTend               -- If it is currently DST then we just erroneously added an hour above,
                  or frm > til and (getdate() >= @DSTstart or getdate() < @DSTend)              -- substract 1 hour to get input datetime in current non-DST timezone,
                    then dateadd(hour, -1, @LocalDatetime)                                      -- regardless of whether it is DST on the date of the input datetime
                else @LocalDatetime
            end
    from @calculation
    where datediff(minute, getutcdate(), getdate()) between frm * 60 and til * 60
      and datepart(year, getdate()) >= since
    order by since desc

    select top 1    -- Determine if it was/will be DST on the date of the input datetime in a similar fashion
        @DSTstart = dateadd(year, datepart(year, @LocalDatetime)-1900, firstPossibleStart),
        @DSTend   = dateadd(year, datepart(year, @LocalDatetime)-1900, firstPossibleEnd),
        @DSTstart = dateadd(day, 6 - (datepart(dw, @DSTstart) + @@datefirst - 2) % 7, @DSTstart),
        @DSTend   = dateadd(day, 6 - (datepart(dw, @DSTend) + @@datefirst - 2) % 7, @DSTend),
        @LocalDatetime = case
                when frm < til and @LocalDatetime >= @DSTstart and @LocalDatetime < @DSTend     -- If it would be DST on the date of the input datetime,
                  or frm > til and (@LocalDatetime >= @DSTstart or @LocalDatetime < @DSTend)    -- add this hour to the input datetime.
                    then dateadd(hour, 1, @LocalDatetime)
                else @LocalDatetime
            end
    from @calculation
    where datediff(minute, getutcdate(), getdate()) between frm * 60 and til * 60
      and datepart(year, @LocalDatetime) >= since
    order by since desc

    return @LocalDatetime
end

This function looks at the difference between local and utc time at the moment it runs to determine which DST-rules to apply. It then knows whether doing datediff(hour, getutcdate(), getdate()) includes a DST hour or not and subtracts it if it does. Then it determines whether it was or will be DST at the date of the input UTC datetime and if so adds the DST hour back.

This comes with one quirk, which is that during the last hour of DST and the first hour of non-DST, the function has no way of determining which it is and assumes the latter. So regardless of input-datetime, if this codes runs during the last hour of DST it will give the wrong outcome. Which means this works 99.9886% of the time.

user_26846600
Related