UTC time to Local Time Zone(Central Time) Conversion MS SQL Sever

Question

This question is a follow up from this question. I have a UTC time column and I want convert to into current local time (Central Time Zone or America/Chicago). I tried to use the function from @Ron Smith's answer, which is [dbo].[fn_UTC_to_DST] function.

In that function, it needs two arguments such as UTC time and offset. I input both like this,

SELECT  dbo.fn_UTC_to_DST([time(UTC)],5) as Date
FROM tbl

Since we are in Day Light Saving time, I am using 5 as my offset. My output looks like this,

2017-09-27 20:55:00.000
2017-09-27 20:56:00.000
2017-09-27 20:57:00.000
2017-09-27 20:58:00.000
...

Which should be (central time),

2017-09-27 09:55:00.000
2017-09-27 09:56:00.000
2017-09-27 09:57:00.000
2017-09-27 09:58:00.000
...

So, I changed @Ron Smith's function like this,

CREATE FUNCTION [dbo].[fn_UTC_to_DST]
(
    @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 = datename(year,@UTC) + '0314' 
set @SSM = dateadd(hour,-5,dateadd(day,datepart(dw,@SSM)*-1+1,@SSM))  -- Changed from 2 to -5
set @FSN = datename(year,@UTC) + '1107'
set @FSN = dateadd(second,-6,dateadd(hour,2,dateadd(day,datepart(dw,@FSN)*-1+1,@FSN)))  -- changed from 1 to -6 

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

GO

However, this still gives me the same result as above. 1. What should I change for Central time? 2. Is there a way to automatically change to -5 during daylight saving time and -6 during standard time?

EDIT:

After looking at the Answer and the reference link from #Siyual,I created the dbo.TZCalendar table and I tried to create a function like this (takes one argument and returns a date from refrence link)

CREATE FUNCTION dbo.ConvertUTCToLocal
(
  @utc DATETIME
)
RETURNS Datetime
AS BEGIN 
  SELECT UTCToLocal = 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)

FROM dbo.TZCalendar 
WHERE CONVERT(DATE,@utc) >= [Year] 
  AND CONVERT(DATE,@utc) < DATEADD(YEAR, 1, [Year])
END
GO

This, does not work. The logic seems right for me, but, I just need a function without SCHEMABINDING (which is done in the reference link). How can I do that?

Answer 1

If you're using SQL Server 2016+ (or Azure SQL Database) this is built in:

SELECT YourInputDatetimeInUTC AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time'

The first one asserts your input is UTC, the second one converts it to US Central time, inclusive of DST when applicable (using a Windows time zone identifier).

However, since you said SQL 2012, I'll recommend my SQL Server Time Zone Support project, where this is a single operation and uses IANA identifiers:

SELECT Tzdb.UtcToLocal(YourInputDatetimeInUTC, 'America/Chicago')

Answer 2

The linked answer (Sql Server Specify time in another timezone) will get you most of the way there, but to answer the rest of your question, you'll have to make some modifications.

Firstly, I would create a DST calendar, since the DST start and end dates are something that we can compute:

CREATE TABLE dbo.TZCalendar
(
  Year          Int PRIMARY KEY,
  UTC_DST_Start SMALLDATETIME NOT NULL,
  UTC_DST_End   SMALLDATETIME NOT NULL
);

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 Year(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 will generate the DST times from the year 2000 to 2067 (this can be expanded based on your needs).

Next, I would create a function to take a DATE in UTC and return the value in either CST or CDT, depending on the time of the year.

Create Function dbo.fnConvertUTCToCT(@UTC DateTime)
Returns DateTime
As Begin
    Declare @Offset Int = 0

    Select  @Offset = Case When @UTC Between UTC_DST_Start And UTC_DST_End Then -5 Else -6 End
    From    dbo.TZCalendar
    Where   Year = Year(@UTC)

    Set @UTC = DateAdd(Hour, @Offset, @UTC)

    Return @UTC
End

Then you can just call that function with any time specified and get the CST or CDT translation returned:

Select   dbo.fnConvertUTCToCT(GetUTCDate())

2017-09-27 12:24:26.377

Answer 3

UTC time to Central time including DST adjustments

This SQL expression converts UTC time to Central Standard time which also adjusts itself for the Day Light Saving Time period.

DATEADD(HOUR,datediff(HOUR,[HTMPOSTEDDATETIME] at time zone 'Central Standard Time',[HTMPOSTEDDATETIME]),[HTMPOSTEDDATETIME]) [JNL Posted Date]