CODEWITHSUNDEEP
sqldst
NitroxDM
NitroxDM

Reputation: 5131

Determine if daylight saving time is active? 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?

Upvotes: 2

Views: 20716

Answers (5)

Derek Wade
Derek Wade

Reputation: 746

using SQL 2016 system table (noted by @anacv),

To get current time for a timezone (DST or PDT) if server/timestamp is GMT

declare @currentDatetime datetime
declare @tzone varchar(100)
set @tzone = 'Pacific Standard Time'
set @currentDatetime = dateadd(MINUTE, (select cast(right(current_utc_offset,2) as int) FROM sys.time_zone_info where name=@tzone) , dateadd(HOUR,(select cast(left(current_utc_offset,3) as int) FROM sys.time_zone_info where name=@tzone), CURRENT_TIMESTAMP))

Upvotes: 2

Anacv
Anacv

Reputation: 66

The sys.time_zone_info table introduced in SQL 2016 allows to determine the offset from UTC and whether DST is currently in effect.

SELECT * FROM sys.time_zone_info

The query result will be:

Upvotes: 5

Boba Fett
Boba Fett

Reputation: 96

This won't work in all cases, but is a quick fix for those in the US w/ DST defined as between the 2nd Sunday of March and the 1st Sunday of November.

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?

Upvotes: 4

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241563

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.

Upvotes: 1

NitroxDM
NitroxDM

Reputation: 5131

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.

Upvotes: 1

Related Questions