mssql-timezone.sql

DECLARE @Timestamp AS DATETIMEOFFSET = '2024-03-03T23:05:00.000Z'
--DECLARE @TimeZone AS NVARCHAR(MAX) = 'Central Europe Standard Time';
DECLARE @TimeZone AS NVARCHAR(MAX) = 'GMT Standard Time';

SELECT DATEPART(
               ISO_WEEK,
               CONVERT(
                   DATETIMEOFFSET,
                       SWITCHOFFSET(
                               @Timestamp,
                               DATEPART(
                                       TZOFFSET ,
                                       @Timestamp AT TIME ZONE @TimeZone)),
                       0));