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