Handle conversion between time zones in SQL Server - part 1

By:   |   Updated: 2014-03-04   |   Comments (22)   |   Related: 1 | 2 | 3 | > Dates


Problem

Time zones have always made the SQL Server professional's life difficult. Before SQL Server 2008, conversion between time zones was completely up to the developer. Simple if you were converting today's date and time from, say, UTC to local time - that is very easy to do on either the server or the client. But not so simple if you were converting points in time both in and out of Daylight Saving Time, never mind to multiple time zones or between two different non-UTC time zones. It gets even more fun when converting between two time zones that both observe Daylight Saving Time but change their clocks on different days of the year.

SQL Server 2008 introduced a new data type called DATETIMEOFFSET (along with a function called SWITCHOFFSET), which was immediately heralded as the solution to all of these time zone conversion issues. The problem here is that DATETIMEOFFSET is not DST-aware (Leonard Lobel talks a bit about this here): it assumes that you've already incorporated any DST adjustments into the offset you specify. In several Connect items you will see discussions about future enhancements that will incorporate DST, but unfortunately even those promises - which haven't been delivered in SQL Server 2014 - only seem to be talking about supporting conversion to and from a *local* time zone with DST. In other words, it would use the operating system to allow adjustments from the local system's time zone for today's date, but wouldn't be very helpful for other dates or when considering other, non-local time zones.

So people started inventing their own solutions (or continuing to use the ones they employed before SQL Server 2008). These usually consist of long, tedious and hard-to-maintain scalar user-defined functions that take a date/time, parse all of its components, determine if it is within the boundaries of the target time zone's DST range, and then adjust by the appropriate number of minutes. These functions often have minor errors in logic, neglect DST rule changes for different years, and are extremely complex even while dealing with only one or a limited number of time zones. I've seen several solutions that have had a different function for each target time zone, or many redundant chunks of logic to deal with each relevant time zone. Many make the mistake of converting a UTC date/time to the local time zone by applying the difference between GETDATE() and GETUTCDATE(), but like other scenarios, this only works when running the code today (or for a date that happens to be in the same DST range). I'd reference some examples, but I don't want to call out any of my peers. :-)

Solution

I have long been a proponent of calendar tables for solving problems like this, among many others - I first wrote about these in 2004. In this series of tips, I'm going to describe some ways to convert data between time zones, with a focus on both minimizing the complexity of the calling code and maximizing performance by using the smallest calendar table possible.

One scenario that is certainly easy to address is the simplest case: you store all of your date/time values in UTC, and need to convert those dates to a single time zone. In this case, we'll use the Eastern time zone of the USA. For this we can use a calendar table with one row per year, where each year has a DST start and a DST end expressed in both UTC and the local time (more on this below). 

CREATE TABLE dbo.TZCalendar
(
  [Year]        DATE PRIMARY KEY,
  UTC_DST_Start SMALLDATETIME NOT NULL,
  UTC_DST_End   SMALLDATETIME NOT NULL,
  ET_DST_Start  AS CONVERT(SMALLDATETIME,DATEADD(HOUR, -4, UTC_DST_Start)),
  ET_DST_End    AS CONVERT(SMALLDATETIME,DATEADD(HOUR, -5, UTC_DST_End))
);

The most complicated part of this scenario is populating the calendar table. Most people would use a loop, but I much prefer building a set-based solution. As I mentioned above, I'm going to assume that you don't need to go back earlier than 2000, and I'm also going to assume that you only need to go 50 years into the future (you may need to adjust for your requirements if they are different). But first I should explain the factors that influence the details of the set-based solution I derive.

For the Eastern time zone, in the Spring, we move from 2 AM to 3 AM local time, at 7 AM UTC ("Spring forward"). And in the Fall, we move from 2 AM to 1 AM local time, at 6 AM UTC ("Fall back"). Thankfully, since 2000 (well, since 1987), there are only two different conditions for determining the dates of these events: until 2006, we Spring forward on the first Sunday in April, and Fall back on the last Sunday in October. In 2007, this changed such that we Spring forward on the second Sunday in March, and fall back on the first Sunday in November. So, for example, in 2014, we will switch to DST on March 9th, and fall back on November 2nd. We use the local time values in the table only to rule out invalid dates (e.g. 2:20 AM, in Eastern Time, on the morning we Spring forward from 2 AM to 3 AM, is not a possible local time, because it never happened).

With all that in mind, here is the set-based solution I came up with: 

SET DATEFIRST 7;
;WITH cte(d,p) AS 
(
  -- all the years from 2000 through 50 years after the current year:
  SELECT TOP (YEAR(GETDATE())-2000+51) DATEADD(YEAR,number,'20000101'),
    CASE WHEN number < 7 THEN 1 ELSE 0 END -- year < 2007 = 1, else 0
    FROM [master].dbo.spt_values WHERE [type] = N'P' ORDER BY number
)
INSERT dbo.TZCalendar([Year],UTC_DST_Start,UTC_DST_End)
SELECT d,
 -- First Sunday in April (< 2007) or second Sunday in March (>= 2007):
 DATEADD(HOUR, 7, DATEADD(DAY,(7-DATEPART(WEEKDAY,DATEADD(MONTH,2+p,d))+1)%7
    +(7*ABS(p-1)),DATEADD(MONTH,2+p,d))),
 -- Last Sunday in October (< 2007) or first Sunday in November (>= 2007):
 DATEADD(HOUR, 6, DATEADD(DAY,(7-DATEPART(WEEKDAY,DATEADD(MONTH,10,d))+1)%7
    -(7*p),DATEADD(MONTH,10,d)))
FROM cte
ORDER BY d;

This creates a table with 65 rows that is a whopping 16 KB in size. As long as there isn't something really strange going on in your system, it should be in memory all of the time (and when it isn't, pulling two pages from disk will be largely inconsequential anyway). The data looks like this: 

Year        DTStart           DTEnd             ET_DST_Start      ET_DST_End
----------  ----------------  ----------------  ----------------  ----------------
2000-01-01  2000-04-02 07:00  2000-10-29 06:00  2000-04-02 03:00  2000-10-29 01:00
2001-01-01  2001-04-01 07:00  2001-10-28 06:00  2001-04-01 03:00  2001-10-28 01:00
2002-01-01  2002-04-07 07:00  2002-10-27 06:00  2002-04-07 03:00  2002-10-27 01:00
...
2013-01-01  2013-03-10 07:00  2013-11-03 06:00  2013-03-10 03:00  2013-11-03 01:00
2014-01-01  2014-03-09 07:00  2014-11-02 06:00  2014-03-09 03:00  2014-11-02 01:00
2015-01-01  2015-03-08 07:00  2015-11-01 06:00  2015-03-08 03:00  2012-11-01 01:00
...
2062-01-01  2062-03-12 07:00  2062-11-05 06:00  2062-03-12 03:00  2062-11-05 01:00
2063-01-01  2063-03-11 07:00  2063-11-04 06:00  2063-03-11 03:00  2063-11-04 01:00
2064-01-01  2064-03-09 07:00  2064-11-02 06:00  2064-03-09 03:00  2064-11-02 01:00

Now, how do we use this data? We can create two simple functions; one that takes a date/time value in UTC, and converts it to Eastern time, and another that takes a date/time value in Eastern time, and converts it to UTC. There are two edge cases that you need to be aware of:

  1. If I pass in a UTC time like 2014-11-02 06:30, and another UTC time like 2014-11-02 07:30, both will yield the same time (2014-11-02 01:30) when converted to Eastern Time. This is because at 1:30 Eastern Daylight Time, it was 06:30 UTC, then at 06:00 UTC (2:00 AM Eastern Daylight Time) the clocks rolled back to 1:00 AM Eastern Standard Time, and half an hour later - at 06:30 UTC - the Eastern time zone happened upon 1:30 AM again. I'll leave it up to you how you want to disambiguate this when displaying these values (since you have the original UTC time, you can tell which ones are four hours apart, and which are five hours apart).
  2. If I pass in a local time like 2014-03-09 02:30, I will return NULL (you could, alternatively, raise an error). This is because that time couldn't have actually happened - at 02:00 Eastern Standard Time we moved the clocks forward to 03:00 Eastern Daylight Time. So any event claiming to have happened inside that hour is bogus. In the function below, I chose to return NULL, but you obviously have other options if you want to change it. 
CREATE FUNCTION dbo.ConvertUTCToLocal
(
  @utc DATETIME
)
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN 
  (
    SELECT UTCToLocal = DATEADD(HOUR, CASE 
 
      -- within Daylight Savings Time
      WHEN @utc >= UTC_DST_Start AND @utc < UTC_DST_End 
      THEN -4 
   
      -- within Standard Time
      ELSE -5 END, @utc)
      
    FROM dbo.TZCalendar 
    WHERE CONVERT(DATE,@utc) >= [Year] 
      AND CONVERT(DATE,@utc) < DATEADD(YEAR, 1, [Year])
  );
GO
CREATE FUNCTION dbo.ConvertLocalToUTC
(
  @local DATETIME
)
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN 
  (
    SELECT LocalToUTC = DATEADD(HOUR, CASE
      -- account for the "skipped" hour that never happened
      WHEN @local >= DATEADD(HOUR,-1,ET_DST_Start)
       AND @local < ET_DST_Start THEN NULL
    
      -- within Daylight Saving Time
      WHEN @local >= ET_DST_Start 
       AND @local < ET_DST_End THEN 4 
   
      -- within Standard Time 
      ELSE 5 END, @local)
      
    FROM dbo.TZCalendar 
    WHERE CONVERT(DATE,DATEADD(HOUR, -5, @local)) >= [Year] 
      AND CONVERT(DATE,DATEADD(HOUR, -5, @local)) < DATEADD(YEAR, 1, [Year])
  );
GO

And we can use these functions in the following way. I've purposely used date values that brush against our edge cases in one direction or the other. 

DECLARE @x TABLE(ID INT PRIMARY KEY, SourceDateTime DATETIME);
INSERT @x(ID, SourceDateTime) VALUES (1, '20131231 23:59'),
  (2, '20140101 00:01'),(3, '20140309 01:59'),(4, '20140309 02:00'),
  (5, '20140309 06:59'),(6, '20140309 07:00'),(7, '20140601 00:00'),
  (8, '20141102 01:59'),(9, '20141102 02:00'),(10,'20141102 02:30'),
  (11,'20141102 03:00'),(12,'20141102 05:59'),(13,'20141102 06:59'),
  (14,'20141102 07:00'),(15,'20141231 18:59'),(16,'20141231 19:01');
SELECT * FROM @x AS x 
CROSS APPLY dbo.ConvertUTCToLocal(x.SourceDateTime) AS l;

Results of the conversion from UTC to local time:

Results of the conversion from UTC to local time


SELECT * FROM @x AS x 
CROSS APPLY dbo.ConvertLocalToUTC(x.SourceDateTime) AS l;

Results of the conversion from local time to UTC:

Results of the conversion from local time to UTC

In my next installment, I'll talk about expanding this solution to allow converting between multiple time zones, rather than just to and from UTC.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2014-03-04

Comments For This Article




Wednesday, January 22, 2020 - 5:20:47 PM - David Wenzel Back To Top (83939)

If I am trying to do this function for central standard time do I just have to change the values in the function from 5 to 6 like below?

SELECT [LocalToUTC] = DATEADD(HOUR, CASE

  -- account for the "skipped" hour that never happened

  WHEN @local >= DATEADD(HOUR,-1,[ET_DST_Start])

   AND @local < [ET_DST_Start] THEN 6

    

  -- within Daylight Saving Time

  WHEN @local >= [ET_DST_Start] 

   AND @local < DATEADD(HOUR,1,[ET_DST_End]) THEN 5 

   

  -- within Standard Time 

  ELSE 6 END, @local)

      

FROM [dbo].[TZCalendar]

WHERE CONVERT(DATE,DATEADD(HOUR, -6, @local)) >= [Year] 

  AND CONVERT(DATE,DATEADD(HOUR, -6, @local)) < DATEADD(YEAR, 1, [Year])

*

Tuesday, January 22, 2019 - 2:54:47 PM - Paul Kemna Back To Top (78845)

Hello. I am trying to modify this code to work for Central time instead.  I have adjusted the UTC_DST_Start and End to 8:00 and 7:00. I have modified the time adjustments for UTC to Central to -5 when in DST and -6 when not. For Central to UTC, I made similar changes to +5 when in and +6 when not. 

However, it does not appear to be working correctly, as '2014-11-02 01:59:00.000' is returning as 2014-11-02 07:59:00.000.

Here are the blocks of code from the two functions:

SELECT DATEADD(HOUR, CASE

    -- account for the "skipped" hour that never happened

    WHEN @local >= DATEADD(HOUR,-1,CT_DST_Start)

    AND @local < CT_DST_Start THEN NULL

    -- within Daylight Saving Time

    WHEN @local >= CT_DST_Start 

    AND @local < CT_DST_End THEN 5 

    -- within Standard Time 

    ELSE 6 END, @local) as result

FROM Utils.DSTCalendar

WHERE CONVERT(DATE,DATEADD(HOUR, -6, @local)) >= [Year] 

    AND CONVERT(DATE,DATEADD(HOUR, -6, @local)) < DATEADD(YEAR, 1, [Year])

SELECT DATEADD(HOUR, CASE 

-- within Daylight Savings Time

WHEN @utc >= UTC_DST_Start AND @utc < UTC_DST_End 

THEN -5 

-- within Standard Time

ELSE -6 END, @utc) as Result

FROM Utils.DSTCalendar

WHERE CONVERT(DATE,@utc) >= [Year] 

AND CONVERT(DATE,@utc) < DATEADD(YEAR, 1, [Year])

What am i doing wrong?  Can you help me?

Thanks.

Tuesday, October 9, 2018 - 8:56:34 AM - Aaron Bertrand Back To Top (77904)

@Jenda I think you'll find that when you use an inline TVF once instead of 5,000 executions of a scalar function, the difference will be even more negligible (how often would you be converting 5,000 date/time values for output - usually you're just using this kind of thing to determine the true begin and end range for a report, so your executions would actually be just 2). If you want to use int instead of date to represent a date, I'm not going to stop you. People do all kinds of things to shave milliseconds off a query.  :-)

Monday, October 8, 2018 - 6:12:25 PM - Jenda Back To Top (77899)

Thanks! This is exactly why I asked. I didn't know about the performance difference between scalar UDFs ad TVFs :-) I did a test of the int versus date with the scalar functions and 5000 executions took 344, 235 and 156 ms with int and 484, 281 and 266 ms with date (three runs, all other things being equal).

CREATE TABLE dbo.TZCalendar
(
  [Year]        int NOT NULL PRIMARY KEY,
  UTC_CET_Start SMALLDATETIME NOT NULL,
  UTC_CET_End   SMALLDATETIME NOT NULL,
  ET_CET_Start  AS CONVERT(SMALLDATETIME,DATEADD(HOUR, 2, UTC_CET_Start)),
  ET_CET_End    AS CONVERT(SMALLDATETIME,DATEADD(HOUR, 1, UTC_CET_End))
);
 
SET DATEFIRST 7;
;WITH cte(yearstart,yr) AS 
(
  -- all the years from 2000 through 50 years after the current year:
  SELECT TOP (YEAR(GETDATE())-1990+51) DATEADD(YEAR,number,'19900101'), Year(DATEADD(YEAR,number,'19900101'))
    FROM [master].dbo.spt_values WHERE [type] = N'P' ORDER BY number
)
INSERT dbo.TZCalendar([Year],UTC_CET_Start,UTC_CET_End)
SELECT yr,
 -- poslední neděli v březnu, kdy se ve 2:00 hodiny posunou o jednu hodinu napřed, na 3:00
 DateAdd(hour, 1, DateAdd( day, -DATEPART(WEEKDAY, DateAdd(day, -1, DateAdd(month, 3, yearstart))), DateAdd(month, 3, yearstart))),
 -- končí poslední neděli v říjnu, kdy se ve 3:00 hodiny posunou o jednu hodinu nazpět, na 2:00
 DateAdd(hour, 2, DateAdd( day, -DATEPART(WEEKDAY, DateAdd(day, -1, DateAdd(month, 10, yearstart))), DateAdd(month, 10, yearstart)))
FROM cte
ORDER BY yr;
go
 
CREATE FUNCTION dbo.ConvertUTCToCExT
(
  @utc DATETIME
)
RETURNS DATETIME
WITH SCHEMABINDING
AS
BEGIN
  Declare @local DATETIME;
  SET @local = (
    SELECT DATEADD(HOUR, CASE 
 
      -- within Daylight Savings Time
      WHEN @utc >= UTC_CET_Start AND @utc < UTC_CET_End 
      THEN +2
   
      -- within Standard Time
      ELSE +1 END, @utc)
      
    FROM dbo.TZCalendar 
    WHERE Year(@utc) = [Year]
  );
  return @local;
END
GO
CREATE FUNCTION dbo.ConvertCExTToUTC
(
  @local DATETIME
)
RETURNS DATETIME
WITH SCHEMABINDING
AS
BEGIN
Declare @utc DATETIME;
  SET @utc =
  (
    SELECT DATEADD(HOUR, CASE
      -- account for the "skipped" hour that never happened
      WHEN @local >= DATEADD(HOUR,-1,ET_CET_Start)
       AND @local < ET_CET_Start THEN NULL
    
      -- within Daylight Saving Time
      WHEN @local >= ET_CET_Start 
       AND @local < ET_CET_End THEN -2
   
      -- within Standard Time 
      ELSE -1 END, @local)
      
    FROM dbo.TZCalendar 
    WHERE Year(DATEADD(HOUR, 1, @local)) = [Year]
  );
  RETURN @utc;
END
 
GO
--------------
CREATE TABLE dbo.TZCalendar_d
(
  [Year]        date NOT NULL PRIMARY KEY,
  UTC_CET_Start SMALLDATETIME NOT NULL,
  UTC_CET_End   SMALLDATETIME NOT NULL,
  ET_CET_Start  AS CONVERT(SMALLDATETIME,DATEADD(HOUR, 2, UTC_CET_Start)),
  ET_CET_End    AS CONVERT(SMALLDATETIME,DATEADD(HOUR, 1, UTC_CET_End))
);
 
SET DATEFIRST 7;
;WITH cte(yearstart,yr) AS 
(
  -- all the years from 2000 through 50 years after the current year:
  SELECT TOP (YEAR(GETDATE())-1990+51) DATEADD(YEAR,number,'19900101'), Year(DATEADD(YEAR,number,'19900101'))
    FROM [master].dbo.spt_values WHERE [type] = N'P' ORDER BY number
)
INSERT dbo.TZCalendar_d([Year],UTC_CET_Start,UTC_CET_End)
SELECT yearstart,
 -- poslední nedeli v breznu, kdy se ve 2:00 hodiny posunou o jednu hodinu napred, na 3:00
 DateAdd(hour, 1, DateAdd( day, -DATEPART(WEEKDAY, DateAdd(day, -1, DateAdd(month, 3, yearstart))), DateAdd(month, 3, yearstart))),
 -- koncí poslední nedeli v ríjnu, kdy se ve 3:00 hodiny posunou o jednu hodinu nazpet, na 2:00
 DateAdd(hour, 2, DateAdd( day, -DATEPART(WEEKDAY, DateAdd(day, -1, DateAdd(month, 10, yearstart))), DateAdd(month, 10, yearstart)))
FROM cte
ORDER BY yr;
go
 
CREATE FUNCTION dbo.ConvertUTCToCExT_d
(
  @utc DATETIME
)
RETURNS DATETIME
WITH SCHEMABINDING
AS
BEGIN
  Declare @local DATETIME;
  SET @local = (
    SELECT DATEADD(HOUR, CASE 
 
      -- within Daylight Savings Time
      WHEN @utc >= UTC_CET_Start AND @utc < UTC_CET_End 
      THEN +2
   
      -- within Standard Time
      ELSE +1 END, @utc)
      
    FROM dbo.TZCalendar_d 
    WHERE CONVERT(DATE,@utc) >= [Year] 
      AND CONVERT(DATE,@utc) < DATEADD(YEAR, 1, [Year])
  );
  return @local;
END
GO
CREATE FUNCTION dbo.ConvertCExTToUTC_d
(
  @local DATETIME
)
RETURNS DATETIME
WITH SCHEMABINDING
AS
BEGIN
Declare @utc DATETIME;
  SET @utc =
  (
    SELECT DATEADD(HOUR, CASE
      -- account for the "skipped" hour that never happened
      WHEN @local >= DATEADD(HOUR,-1,ET_CET_Start)
       AND @local < ET_CET_Start THEN NULL
    
      -- within Daylight Saving Time
      WHEN @local >= ET_CET_Start 
       AND @local < ET_CET_End THEN -2
   
      -- within Standard Time 
      ELSE -1 END, @local)
      
    FROM dbo.TZCalendar_d 
    WHERE CONVERT(DATE,DATEADD(HOUR, 2, @local)) >= [Year] 
      AND CONVERT(DATE,DATEADD(HOUR, 2, @local)) < DATEADD(YEAR, 1, [Year])
  );
  RETURN @utc;
END
GO
 
 
select top 5000 *, dbo.ConvertCExTToUTC(DateCreated) from IssuedInvoices
print '-----------------'
select top 5000 *, dbo.ConvertCExTToUTC_d(DateCreated) from IssuedInvoices

I'll post the comparison between int and date primary key in the TZCalendar with TVFs tomorrow.

Monday, October 8, 2018 - 1:55:56 PM - Aaron Bertrand Back To Top (77895)

@Jenda Do you have any benchmarking that shows comparing an integer is faster than a date? That may have been true decades ago but I just don't think it holds true on today's computers. Also see https://dba.stackexchange.com/a/15656/1186

On the other hand, scalar user-defined functions like you are proposing definitely do perform worse than the inline table-valued function I've provided. The reason is that a scalar UDF needs to be executed for every input row, whereas the TVF is expanded and incorporated into the plan. Try some of your own performance tests and see, but this is documented quite readily:

https://www.mssqltips.com/sqlservertip/4689/understand-the-performance-behavior-of-sql-server-scalar-user-defined-functions/

https://www.mssqltips.com/sqlservertip/4772/refactor-sql-server-scalar-udf-to-inline-tvf-to-improve-performance/

Monday, October 8, 2018 - 1:03:47 PM - Jenda Back To Top (77894)

I do believe it's cheeper to compare two integers by equals than to compute two dates and then compare four dates by <=

If you use the year number as the primary key, you may replace

 CONVERT(DATE,@utc) >= [Year] AND CONVERT(DATE,@utc) < DATEADD(YEAR, 1, [Year])

by

 Year(@utc) = [Year]

The table is just an internal structure used by those two often used functions so efficiency is more important than clarity.

Also it seems to me that it's more efficient and more convenient to have them as scalar functions.

SELECT Id, Foo, Bar, dbo.ConvertLocalToUTC(CreatedDate) FROM dbo.Table

Let me know what you think.

Monday, October 8, 2018 - 12:20:41 PM - Aaron Bertrand Back To Top (77893)

@Jenda I'm not quite sure I understand the benefits of the alternate designs you're proposing? I feel like if you are representing a date as your primary key, the data type makes a lot more sense as a date than an integer. And the functions only return a single row when you pass a single value in - they are not scalar-valued functions, and they perform very well when used against larger result sets.

Monday, October 8, 2018 - 9:59:46 AM - Jenda Back To Top (77889)

Any reason you do not use the year (as integer) as the primary key of the table and they Year(@utc) = [Year] and Year(DATEADD(HOUR, -5, @local)) = [Year] in the functions?

Any reason your functions return one-row-one-column tables instead of the datetimes themselves?

Monday, August 21, 2017 - 1:11:08 PM - Mike Back To Top (65123)

I know this was an old post but I was wondering what you thought about my personal solution to this issue.  Rather then creating a lookup table which wouldn't really work well in an enteprise perspective.  I created this function and it seems to not be causing any bottlenecks or slowing down my queries.  I'm a C# programmer and I was wondering what you thought from a DBA perspective.

 

USE [WebApplicationLogging]

GO

/****** Object:  UserDefinedFunction [dbo].[fn_UTC_to_EST]    Script Date: 8/21/2017 1:09:14 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:      ~~~~~~~

-- Create date: 8/21/2017

-- Description: Converts UTC to DST

--              based on passed Standard offset

-- =============================================

ALTER FUNCTION [dbo].[fn_UTC_to_EST]

(

    @UTC datetime,

    @StandardOffset int

)

RETURNS datetime

AS

BEGIN

 

declare 

    @DST datetime,

    @SSM datetime, -- Second Sunday in March

    @FSN datetime  -- First Sunday in November

-- get DST Range

set @SSM = DATEADD(dd,7 + (6-(DATEDIFF(dd,0,DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 2,0))%7)),DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 2,0))+'02:00:00' 

set @FSN = DATEADD(dd, (6-(DATEDIFF(dd,0,DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 10,0))%7)),DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 10,0)) +'02:00:00'

 

-- add an hour to @StandardOffset if @UTC is in DST range

if @UTC between @SSM and @FSN

    set @StandardOffset = @StandardOffset + 1

 

-- convert to DST

set @DST = dateadd(hour,@StandardOffset,@UTC)

 

-- return converted datetime

return @DST

 

END

 

Thursday, February 16, 2017 - 9:23:46 AM - josh Back To Top (46397)

 What issues do you see with using .NET inside of CLR for solving this problem?  Is it just in case your shop will not allow CLR?

 

Thursday, July 7, 2016 - 1:44:52 PM - Bruce Leehan Back To Top (41827)

Hi Aaron,

Disregard my last post, I got it figured out! Thank you again for contributing this much needed solution!

Bruce

Thursday, July 7, 2016 - 10:58:05 AM - Bruce Leehan Back To Top (41826)

Hi Aaron, thank you for this!

I have successfully created the table and the function in my report server, but I am trying to figure out how I can use it in a select statement and I am not having any luck. (my apologies as I'm a sql novice)

here is my sample code:

SELECT 

i.IncidentNumber

,ct.CallType

,(select EntryValue from Report.dbo.ValidationSetEntry

  where EntryID = li.vsIncidentType) as IncidentType

,u.UnitNumber

,u.VehicleNumber

,s.Status

,l.LogDatetime --<< this is the UTC 'start time' that I need converted to local

,l.EndDatetime --<< this is the UTC 'end time' that I need converted to local

FROM 

Report.Unit.UnitLog l inner join 

Report.Unit.Unit u On l.UnitID=u.UnitID inner Join

Report.Call.Incident i On l.CallID=i.CallID Left Join

Report.dbo.CallForServiceIncident ci On i.IncidentID=ci.IncidentID Left Join

Report.dbo.CallForService cfs On ci.CFSID=cfs.CFSID Left Join

Report.dbo.CallType ct On cfs.CallTypeID=ct.CallTypeID Left Join

Report.dbo.LEIncident li On ci.IncidentID=li.IncidentID Left Join

Report.General.Status s On l.StatusID=s.StatusID 

WHERE

l.ActionID NOT IN('6','21','24')  AND l.CallID ='1252666' 

ORDER BY

l.LogDatetime ASC 

 

I can't figure out how to use the function in order to return local times in my query. Any suggestions? Thanks again!

 

 

 

 

Friday, March 18, 2016 - 11:30:03 AM - Aaron Bertrand Back To Top (40990)

 

George, specifically, you can change that CTE with spt_values to the following and get the same results:

 

;WITH cte(d,p) AS 
(
  -- all the years from 2000 through 50 years after the current year:
  SELECT DATEADD(YEAR,number,'20000101'),
    CASE WHEN number < 7 THEN 1 ELSE 0 END -- year < 2007 = 1, else 0
    FROM
    (
      SELECT TOP (YEAR(GETDATE())-2000+51) number = ROW_NUMBER() OVER (ORDER BY [object_id])-1
      FROM sys.all_columns ORDER BY number
    ) AS x
)

Friday, March 18, 2016 - 8:58:44 AM - Aaron Bertrand Back To Top (40983)

 

George, you really just need any table that can help you generate a sequence. You should be able to use ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_columns, for example, and perform a cross join if that isn't enough rows.

Friday, March 18, 2016 - 7:36:32 AM - George Sandoval Back To Top (40981)

 Hello Aaron.  I am trying to implement your timezone fix on my instance: Microsoft SQL Azure (RTM) - 12.0.2000.8. But unfortunately for me the [master].dbo.spt_values table does not exist.  I've done a lot of searching and your solution is the best that I've found and I really would like to use it. Can you suggest anything to help me get around my issue?  Thanks!

 

Wednesday, February 10, 2016 - 9:31:00 AM - Jim Carnicelli Back To Top (40639)

No worries. Good work. I ended up with a similar implementation to support any time zone instead of one. Your model helped validate the approach.

I'm ever amazed at how crazy the simple question of "what time is it" actually is, when you dig deeper. Thanks for your contribution.

Tuesday, February 9, 2016 - 9:04:05 PM - Aaron Bertrand Back To Top (40635)

Hi Jim, sorry, my memory is muddy, and I'm not sure how that data was posted that way. Errant copy and paste from output for a different DATEFIRST maybe. In cny case, it has been corrected.

Tuesday, February 9, 2016 - 3:35:57 PM - Jim Carnicelli Back To Top (40633)

The time zone begin/end table appears to have incorrect data. The first Sunday in April of 2000 is the second of April, not the sixth. April sixth is a Thursday.

Friday, August 28, 2015 - 1:00:07 PM - Aaron Bertrand Back To Top (38562)

George, have you read all the parts in this series? No, there is nothing Microsoft has built that handles this. But really, it's not as big a problem as you make it out to be:

  1. Regardless of how many time zones you support, it's just additional rows in the same table. It's no more complex than if you add more customers or orders or widgets.
  2. The rules for DST really don't change that often. You can build your tables out years into the future, and make changes if and when certain jurisdictions make changes (and afaik they always do this quite far in advance).

Friday, August 28, 2015 - 11:50:09 AM - George Back To Top (38561)

Nice artical, thank you.  Timezones become much more complicated if you have a global application and may need to convert to any timezone in the world on the fly.  I have such an app and am able to provide the timezone needed as a parameter, but this will require a master table mapping every timezone that exists along with all DST changes.  Since the properties (DST) of a timezone can change anytime, this becomes a maintenance problem.  I would have thought MS would have provided a built in library for this by now.

Can you recomend a solution for this?

Thanks.

Wednesday, May 20, 2015 - 1:22:45 PM - Cobra CAO Back To Top (37237)

Aaron,

   I was playing around with your solution and trying to convert it to CST (vs your EST).  However I got to a point where I was still off an hour and when I'd pass '20140309 02:00' into ConvertLocalToUTC I would get a NULL.

 

Since you are already assuming that the function is being called where DST is applicable, wouldnt the following solution work:

CREATE FUNCTION [dbo].[LocalTimeToUTC] (@localDate AS DATETIME)  

RETURNS INT

AS

BEGIN 

     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)

 

     RETURN DATEDIFF (HH, GETUTCDATE(), GETDATE()) + CASE WHEN @LocalDate BETWEEN @DSTStartDate AND @DSTEndDate THEN 1 ELSE 0 END

END

GO

Wednesday, June 4, 2014 - 6:36:51 AM - H.K.Mehta Back To Top (32072)

i am getting the problem for to calculate the time. my query is that i have two fields in data base time1 char(11), and time2 char(11)

and the time is in 24 hours format. i want to get the time difference between them.eg

 

time1 = 12:30

time2 = 13:10

difference (time1-time2)

i want difference 13:10 - 12:30 and it must be 0:40 Minutes.

 

kindly advice me how i can get this detail froma SQL program.

 

Thanks

 

H.K.Mehta














get free sql tips
agree to terms