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



Question:

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  


Solution:1

Assuming you are using SQL 2005 upwards, you can develop a SQL CLR function to take a UTC date and convert to the local date.

This link is an MSDN How-To explaining how you can create a scalar UDF in C#.

Create a SQL function along the lines of

[SqlFunction()]  public static SqlDateTime ConvertUtcToLocal(SqlDateTime utcDate)   {      // over to you to convert SqlDateTime to DateTime, specify Kind      // as UTC, convert to local time, and convert back to SqlDateTime  }  

Your sample above would then become

set @localdate = dbo.ConvertUtcToLocal(@utcdate)  

SQL CLR has its overheads in terms of deployment, but I feel cases like this are where it fits in best.


Solution:2

I've been waiting for 5 years for a more elegant solution but since one has not emerged, I'll post what I've been using thus far...

CREATE FUNCTION [dbo].[UDTToLocalTime](@UDT AS DATETIME)    RETURNS DATETIME  AS  BEGIN   --====================================================  --Set the Timezone Offset (NOT During DST [Daylight Saving Time])  --====================================================  DECLARE @Offset AS SMALLINT  SET @Offset = -5    --====================================================  --Figure out the Offset Datetime  --====================================================  DECLARE @LocalDate AS DATETIME  SET @LocalDate = DATEADD(hh, @Offset, @UDT)    --====================================================  --Figure out the DST Offset for the UDT Datetime  --====================================================  DECLARE @DaylightSavingOffset AS SMALLINT  DECLARE @Year as SMALLINT  DECLARE @DSTStartDate AS DATETIME  DECLARE @DSTEndDate AS DATETIME  --Get Year  SET @Year = YEAR(@LocalDate)    --Get First Possible DST StartDay  IF (@Year > 2006) SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-03-08 02:00:00'  ELSE              SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-04-01 02:00:00'  --Get DST StartDate   WHILE (DATENAME(dw, @DSTStartDate) <> 'sunday') SET @DSTStartDate = DATEADD(day, 1,@DSTStartDate)      --Get First Possible DST EndDate  IF (@Year > 2006) SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-11-01 02:00:00'  ELSE              SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-10-25 02:00:00'  --Get DST EndDate   WHILE (DATENAME(dw, @DSTEndDate) <> 'sunday') SET @DSTEndDate = DATEADD(day,1,@DSTEndDate)    --Get DaylightSavingOffset  SET @DaylightSavingOffset = CASE WHEN @LocalDate BETWEEN @DSTStartDate AND @DSTEndDate THEN 1 ELSE 0 END    --====================================================  --Finally add the DST Offset   --====================================================  RETURN DATEADD(hh, @DaylightSavingOffset, @LocalDate)  END        GO  

Notes:

This is for North American servers that observer Daylight Saving Time. Please change the variable @Offest to the Timezone offset of the server running the SQL function (While NOT Observing the Daylight Savings time)...

--====================================================  --Set the Timezone Offset (NOT During DST [Daylight Saving Time])  --====================================================  DECLARE @Offset AS SMALLINT  SET @Offset = -5  

As the DST rules change update them here...

--Get First Possible DST StartDay  IF (@Year > 2006) SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-03-08 02:00:00'  ELSE              SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-04-01 02:00:00'  --Get DST StartDate   WHILE (DATENAME(dw, @DSTStartDate) <> 'sunday') SET @DSTStartDate = DATEADD(day, 1,@DSTStartDate)      --Get First Possible DST EndDate  IF (@Year > 2006) SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-11-01 02:00:00'  ELSE              SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-10-25 02:00:00'  --Get DST EndDate   WHILE (DATENAME(dw, @DSTEndDate) <> 'sunday') SET @DSTEndDate = DATEADD(day,1,@DSTEndDate)  

Cheers,


Solution:3

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?


Solution:4

You can use my SQL Server Time Zone Support project to convert between IANA standard time zones, as listed here.

Example:

SELECT Tzdb.UtcToLocal('2015-07-01 00:00:00', 'America/Los_Angeles')  


Solution:5

GETUTCDATE() just gives you the current time in UTC, any DATEADD() you do to this value will not include any daylight savings time shifts.

Your best bet is build your own UTC conversion table or just use something like this:

http://www.codeproject.com/KB/database/ConvertUTCToLocal.aspx


Solution:6

Here is a function (again US ONLY) but it is a bit more flexible. It will convert a UTC date to the server local time. It starts by adjusting the appointment date based on the current offset and then adjusts based on the difference of the current offset and the offset of the date of the appointment.

CREATE FUNCTION [dbo].[fnGetServerTimeFromUTC]  (      @AppointmentDate AS DATETIME,       @DateTimeOffset DATETIMEOFFSET  )  RETURNS DATETIME  AS  BEGIN      --DECLARE @AppointmentDate DATETIME;      --SET @AppointmentDate = '2016-12-01 12:00:00'; SELECT @AppointmentDate;        --Get DateTimeOffset from Server      --DECLARE @DateTimeOffset; SET @DateTimeOffset = SYSDATETIMEOFFSET();      DECLARE @DateTimeOffsetStr NVARCHAR(34) = @DateTimeOffset;        --Set a standard DatePart value for Sunday (server configuration agnostic)      DECLARE @dp_Sunday INT = 7 - @@DATEFIRST + 1;        --2006 DST Start First Sunday in April (earliest is 04-01) Ends Last Sunday in October (earliest is 10-25)      --2007 DST Start Second Sunday March (earliest is 03-08) Ends First Sunday Nov (earliest is 11-01)      DECLARE @Start2006 NVARCHAR(6) = '04-01-';      DECLARE @End2006 NVARCHAR(6) = '10-25-';      DECLARE @Start2007 NVARCHAR(6) = '03-08-';      DECLARE @End2007 NVARCHAR(6) = '11-01-';        DECLARE @ServerDST SMALLINT = 0;      DECLARE @ApptDST SMALLINT = 0;      DECLARE @Start DATETIME;      DECLARE @End DATETIME;        DECLARE @CurrentMinuteOffset INT;         DECLARE @str_Year NVARCHAR(4) = LEFT(@DateTimeOffsetStr,4);      DECLARE @Year INT = CONVERT(INT, @str_Year);        SET @CurrentMinuteOffset = CONVERT(INT, SUBSTRING(@DateTimeOffsetStr,29,3)) * 60 + CONVERT(INT, SUBSTRING(@DateTimeOffsetStr,33,2)); --Hours + Minutes        --Determine DST Range for Server Offset      SET @Start = CASE           WHEN @Year <= 2006 THEN CONVERT(DATETIME, @Start2006 + @str_Year + ' 02:00:00')          ELSE CONVERT(DATETIME, @Start2007 + @str_Year + ' 02:00:00')          END;      WHILE @dp_Sunday <> DATEPART(WEEKDAY, @Start) BEGIN          SET @Start = DATEADD(DAY, 1, @Start)      END;        SET @End = CASE           WHEN @Year <= 2006 THEN CONVERT(DATETIME, @End2006 + @str_Year + ' 02:00:00')          ELSE CONVERT(DATETIME, @End2007 + @str_Year + ' 02:00:00')          END;      WHILE @dp_Sunday <> DATEPART(WEEKDAY, @End) BEGIN          SET @End = DATEADD(DAY, 1, @End)      END;        --Determine Current Offset based on Year      IF @DateTimeOffset >= @Start AND @DateTimeOffset < @End SET @ServerDST = 1;        --Determine DST status of Appointment Date      SET @Year = YEAR(@AppointmentDate);        SET @Start = CASE           WHEN @Year <= 2006 THEN CONVERT(DATETIME, @Start2006 + @str_Year + ' 02:00:00')          ELSE CONVERT(DATETIME, @Start2007 + @str_Year + ' 02:00:00')          END;      WHILE @dp_Sunday <> DATEPART(WEEKDAY, @Start) BEGIN          SET @Start = DATEADD(DAY, 1, @Start)      END;        SET @End = CASE           WHEN @Year <= 2006 THEN CONVERT(DATETIME, @End2006 + @str_Year + ' 02:00:00')          ELSE CONVERT(DATETIME, @End2007 + @str_Year + ' 02:00:00')          END;      WHILE @dp_Sunday <> DATEPART(WEEKDAY, @End) BEGIN          SET @End = DATEADD(DAY, 1, @End)      END;        --Determine Appointment Offset based on Year      IF @AppointmentDate >= @Start AND @AppointmentDate < @End SET @ApptDST = 1;        SET @AppointmentDate = DATEADD(MINUTE, @CurrentMinuteOffset + 60 * (@ApptDST - @ServerDST), @AppointmentDate)        RETURN @AppointmentDate  END  GO  


Solution:7

Bobman's answer is close, but has a couple bugs: 1) You must compare local DAYLIGHT time (instead of local STANDARD time) to the Daylight Saving End DateTime. 2) SQL BETWEEN is Inclusive so you should be comparing using ">= and <" instead of BETWEEN.

Here is a working modified version along with some test cases: (Again, this only works for United States)

-- Test cases:  -- select dbo.fn_utc_to_est_date('2016-03-13 06:59:00.000') -- -> 2016-03-13 01:59:00.000 (Eastern Standard Time)  -- select dbo.fn_utc_to_est_date('2016-03-13 07:00:00.000') -- -> 2016-03-13 03:00:00.000 (Eastern Daylight Time)  -- select dbo.fn_utc_to_est_date('2016-11-06 05:59:00.000') -- -> 2016-11-06 01:59:00.000 (Eastern Daylight Time)  -- select dbo.fn_utc_to_est_date('2016-11-06 06:00:00.000') -- -> 2016-11-06 01:00:00.000 (Eastern Standard Time)  CREATE FUNCTION [dbo].[fn_utc_to_est_date]  (      @utc datetime  )  RETURNS datetime  as  begin      -- set offset in standard time (WITHOUT daylight saving time)      declare @offset smallint      set @offset = -5  --EST        declare @localStandardTime datetime      SET @localStandardTime = dateadd(hh, @offset, @utc)        -- DST in USA starts on the second sunday of march and ends on the first sunday of november.      -- DST was extended beginning in 2007:      --   https://en.wikipedia.org/wiki/Daylight_saving_time_in_the_United_States#Second_extension_.282005.29      -- If laws/rules change, obviously the below code needs to be updated.        declare @dstStartDate datetime,              @dstEndDate datetime,              @year int      set @year = datepart(year, @localStandardTime)        -- get the first possible DST start day      if (@year > 2006) set @dstStartDate = cast(@year as char(4)) + '-03-08 02:00:00'      else              set @dstStartDate = cast(@year as char(4)) + '-04-01 02:00:00'      while ((datepart(weekday,@dstStartDate) != 1)) begin --while not sunday          set @dstStartDate = dateadd(day, 1, @dstStartDate)      end        -- get the first possible DST end day      if (@year > 2006) set @dstEndDate = cast(@year as char(4)) + '-11-01 02:00:00'      else              set @dstEndDate = cast(@year as char(4)) + '-10-25 02:00:00'      while ((datepart(weekday,@dstEndDate) != 1)) begin --while not sunday          set @dstEndDate = dateadd(day, 1, @dstEndDate)      end        declare @localTimeFinal datetime,              @localTimeCompare datetime      -- if local date is same day as @dstEndDate day,      -- we must compare the local DAYLIGHT time to the @dstEndDate (otherwise we compare using local STANDARD time).      -- See: http://www.timeanddate.com/time/change/usa?year=2016      if (datepart(month,@localStandardTime) = datepart(month,@dstEndDate)              and datepart(day,@localStandardTime) = datepart(day,@dstEndDate)) begin          set @localTimeCompare = dateadd(hour, 1, @localStandardTime)      end      else begin          set @localTimeCompare = @localStandardTime      end        set @localTimeFinal = @localStandardTime        -- check for DST      if (@localTimeCompare >= @dstStartDate and @localTimeCompare < @dstEndDate) begin          set @localTimeFinal = dateadd(hour, 1, @localTimeFinal)      end        return @localTimeFinal  end  


Solution:8

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


Solution:9

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.


Solution:10

I recently had to do the same thing. The trick is figuring out the offset from UTC, but it's not a hard trick. You simply use DateDiff to get the difference in hours between local and UTC. I wrote a function to take care of this.

Create Function ConvertUtcDateTimeToLocal(@utcDateTime DateTime)  Returns DateTime  Begin      Declare @utcNow DateTime      Declare @localNow DateTime      Declare @timeOffSet Int        -- Figure out the time difference between UTC and Local time      Set @utcNow = GetUtcDate()      Set @localNow = GetDate()      Set @timeOffSet = DateDiff(hh, @utcNow, @localNow)         DECLARE @localTime datetime         Set @localTime = DateAdd(hh, @timeOffset, @utcDateTime)         -- Check Results      return @localTime     End  GO  

This does have on crucial short coming: If a time zone uses a fractional offset, such as Nepal which is GMT+5:45, this will fail because this only deals with whole hours. However, it should fit your needs just fine.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »