First up your database table doesn't make dealing with daylight savings times easy as such you will need multiple steps to to convert UTC/GMT to local time.
Start with creating some variables to hold GMTOFFSET, DAYLIGHTBIAS, STANDARDMONTH/DAY/HOUR/MINUTE/SECOND and DAYLIGHTMONTH/DAY/HOUR/MINUTE/SECOND
You then need to select the data from the WORLDTZ table into those variables like;
SELECT @GMTOFFEST=GMTOFFSET, @DAYLIGHTBIAS=DAYLIGHTBIAS, ...
FROM WORLDTZ
WHERE WORLDTZID= @WORLDTZID
Next get the local datetime;
SET @LOCALDATETIME = DATEADD(n, @GMTOFFEST, @EVENTDATETIME)
You now have a datetime value that does not take into account daylight savings.
You now need to adjust the
@LOCALDATETIME
variable for daylight savings if it is applicable.
Look at the STANDARDMONTH/DAY/HOUR/MINUTE/SECOND and DAYLIGHTMONTH/DAY/HOUR/MINUTE/SECOND variables if they are all the same you don't need to adjust the
@LOCALDATETIME
variable if that are different you need to determine if your event datetime value falls within the daylight savings time for the timezone.
To determine the daylight savings start you need the year from the
@EVENTDATETIME
variable and then combine that with the DAYLIGHTMONTH/DAY/HOUR/MINUTE/SECOND vairables to give a start datetime for daylight savings i.e.
@DAYLIGHTSTART
now you need to determine if daylight savings starts and ends in the same year.
For northern hemisphere daylight savings
if STANDARDMONTH > DAYLIGHTMONTH then
Combine STANDARDMONTH/DAY/HOUR/MINUTE/SECOND and year from <code>@EVENTDATETIME</code> to give <code>@DAYLIGHTEND</code>
if @LOCALDATETIME >= @DAYLIGHTSTART AND @LOCALDATETIME <= @DAYLIGHTEND then
SET @LOCALDATETIME = DATEADD(n, @DAYLIGHTBIAS, @LOCALDATETIME)
For southern hemisphere daylight savings
if STANDARDMONTH < DAYLIGHTMONTH then
Combine STANDARDMONTH/DAY/HOUR/MINUTE/SECOND and year from @EVENTDATETIME to give @DAYLIGHTEND
Add 1 year to @DAYLIGHTEND to get @DAYLIGHTENDNEXTYEAR
if @LOCALDATETIME < @DAYLIGHTEND OR (@LOCALDATETIME >= @DAYLIGHTSTART AND @LOCALDATETIME <= @DAYLIGHTENDNEXTYEAR) then
SET @LOCALDATETIME = DATEADD(n, @DAYLIGHTBIAS, @LOCALDATETIME)
Or you could change the WORLDTZ table to reflect something closer to the article you linked to as one other problem your current table has is that it doesn't take into account countries that may change the date they enter end leave daylight savings which does occur for various reasons.