Geek asks and answers

Determine if daylight saving time is included? SQL Server

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?

+1
source share
4 answers

Now I am 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

This works, but if the server moves to a different time zone or does not soak daylight saving time, I will be hosed.

+1
source

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 differently. They start and stop at different times of the day. A simple offset and the DST flag are not enough for reliable conversion.

, America/Los_Angeles, . SQL .

+1

, , DST, 2- 1- .

DECLARE @Date datetime2 = '2018-11-04 02:01:00'

SELECT 
    CASE 
        WHEN @Date between
            DATETIMEFROMPARTS(YEAR(@Date), 3, (8 - DATEPART(WEEKDAY, DATEFROMPARTS(YEAR(@Date), 3, 1))) + 8, 02, 00, 00, 00)
            and
            DATETIMEFROMPARTS(YEAR(@Date), 11, (8 - DATEPART(WEEKDAY, DATEFROMPARTS(YEAR(@Date), 11, 1))) + 1, 02, 00, 00, 00)
        THEN 1
        ELSE 0
    END --Is it dst?
0

sys.time_zone_info, SQL 2016, UTC , .

SELECT * FROM sys.time_zone_info

:

0

More articles:


All Articles