Determine if daylight saving time is active? SQL S

Question

I have a table with users, UTC time offset, and if they observe daylight saving time. Is there a built in way to get the correct user time?

Answer 1

Right now I'm doing this:

SELECT 
CASE USEDAYLIGHTSAVING
    WHEN 1 THEN
        CASE DATEDIFF(HH,GETUTCDATE(),GETDATE())
                -- I know the server is set to Mountan time and follows daylight saving time
                -- MDT = -6
                -- MST = -7
            WHEN -6 THEN 
                DATEADD(HH,TIMEZONEOFFSET+1,GETUTCDATE())
            ELSE 
                DATEADD(HH,TIMEZONEOFFSET,GETUTCDATE())
        END
    ELSE
            DATEADD(HH,TIMEZONEOFFSET,GETUTCDATE())
    END
FROM 
USERS

It works but if the server get's moved to another timezone or doesn't fallow daylight saving time I'm hosed.

Answer 2

I have a table with users, UTC time offset, and if they observe daylight saving time. Is there a built in way to get the correct user time?

Different time zones around the world observe DST in different ways. They start and stop at different times of the day. Simply having an offset and DST flag is not enough to reliably convert.

Instead, you need a time zone identifier, such as America/Los_Angeles, or one of the others listed here. Once you have that, you can use my SQL Server Time Zone Support project to do the conversions.