How to convert an unsupported MSSQL datetimeoffset value at time zone 'Europe/Amsterdam' to a timestamp at time zone UTC?

[Alban Hertroys 2]

We have several source tables with datetimeoffsets, where DST changes cause times between 2:00 and 3:00 am to either overlap or be missing if converted to a timestamp. Hence, we need the base views in TDV to have this value available at UTC, where the times are continuous and non-ambiguous.

[Alban Hertroys 2]

I currently use this procedure to accomplish this, but it doesn't look very efficient... Suggestions for improvement (or for a more direct conversion) are still very much appreciated.

PROCEDURE DateTimeOffset_to_UTC( IN AmsDTOffset VARCHAR(37), OUT UTCTimestamp TIMESTAMP)BEGIN SELECT TIMESTAMPADD(SQL_TSI_hour , CAST(CASE SUBSTRING(AmsDTOffset, 29, 1) WHEN '+' THEN -1 ELSE +1 END * HOUR(CAST(CONCAT(RIGHT(AmsDTOffset, 5), ':00') as TIME)) as INTEGER) , CAST(LEFT(AmsDTOffset, 26) as TIMESTAMP) ) AS x INTO UTCTimestamp FROM (SELECT 1 as x) dual ;END