How to covert UTC time to CST

Question

I have a dataset recorded in UTC format. I want to covert it and add a field as CST. I used

convert(datetime, switchoffset(convert(datetimeoffset, @UTCTime), datename(TzOffset, sysdatetimeoffset()))) to covert CST. However, I found it changed the time at 5:00:00 (UTC) and it should be changed at 7:00:00 (UTC) (2024-11-03).

why did the script above make the wrong time?

Thanks.

Answer 1

assuming your server is set to CST time, then your code looks ok. are you sure your UTC datetimes are correct. it does not handle day light saving time, if the date is not in the same offset as today. you should use the at time zone feature which does.

my server is in PST (-8 rather than CST -6):

declare @d datetime = cast('2024-11-06T05:00:00' as datetime) -- UTC datetime
select @d as UTC 
	,convert(datetime, switchoffset(convert(datetimeoffset, @d), datename(TzOffset, sysdatetimeoffset()))) as PST
	,cast((@d AT TIME ZONE 'UTC') AT TIME ZONE 'Central Standard Time' as datetime) as CST
-----------------------------------------------------------------------
UTC                     PST                     CST
2024-11-06 05:00:00.000	2024-11-05 21:00:00.000 2024-11-05 23:00:00.000