See the following this article.
Converts an inputdate to the corresponding datetimeoffset value in the target time zone.

AT TIME ZONE 'Pacific Standard Time'
Converts a datetime into a datetimeoffset with the proper UTC offset (includes DST handling).
The result (datetimeoffset) shows the correct Eastern local time, even during daylight transitions

AT TIME ZONE internally uses Windows timezone data, so no manual offset needed.
Use timezone names, not abbreviations (EST, CST, etc.) — those are ambiguous and Works only on SQL Server 2016+.