T-SQL: Check if a given date is in daylight savings or not

Question

Using just pure t-sql, I need to check whether a given date is under daylight savings or not for the Pacific Time Zone. How do I do that?

Answer 1

Taking Jelgab's answer a step further, (this will only work for United States), but this is how I'm doing it.

For a given date (my examples are 3/1/23 and 4/1/23)

SELECT (DATEPART(TZOFFSET,CONVERT(DATETIME,'03/01/2023') AT TIME ZONE 'US Eastern Standard Time') / 60) + 5 AS IsDST-- off

SELECT (DATEPART(TZOFFSET,CONVERT(DATETIME,'04/01/2023') AT TIME ZONE 'US Eastern Standard Time') / 60) + 5 AS IsDST-- on

As others have said, you still have to know if DST is observed, but this will tell you whether or not the date you are concerned about is inside the DST window. If you are concerned about the specific date and time, just replace the CONVERT(DATETIME,'03/01/2023') piece with your DateTime value.

SELECT (DATEPART(TZOFFSET,CONVERT(DATETIME,'03/12/2023 01:59') AT TIME ZONE 'US Eastern Standard Time') / 60) + 5 AS IsDST-- off

SELECT (DATEPART(TZOFFSET,CONVERT(DATETIME,'03/12/2023 02:00') AT TIME ZONE 'US Eastern Standard Time') / 60) + 5 AS IsDST-- on

Answer 2

This is a question from 2015 but, if you can use SQL Server 2016, I use a quick function that I created for such cases and for zone conversions:

Create Function GetOffsetValue( @dateToCheck DateTime2, @timeZoneId VarChar( 60 ) ) Returns Int
As Begin
    Return DatePart( TzOffset, @dateToCheck At Time Zone @timeZoneId ) --In minutes. Divide by 60 if more convenient
End

Examples:

Select dbo.GetOffsetValue( Convert( DateTime2, '2019-01-01 13:00:00' ), 'Pacific Standard Time' ) --  -480
Select dbo.GetOffsetValue( Convert( DateTime2, '2019-10-01 13:00:00' ), 'Pacific Standard Time' ) --  -420

You can then check whether the value is -480 minutes (DST Off) or -420 minutes (DST On) and you can also use it for other purposes.

Answer 3

The start and end values are known, so you can build a DST table that has the start and end times per year and check against that.

This article was referenced for this solution: http://www.mssqltips.com/sqlservertip/3173/handle-conversion-between-time-zones-in-sql-server--part-1/

Create Table TZCalendar
(
    Year            Int             Primary Key,
    UTC_DST_Start   SmallDateTime   Not Null,
    UTC_DST_End     SmallDateTime   Not Null,
    PT_DST_Start    As  Convert(SmallDateTime, DateAdd(Hour, -7, UTC_DST_Start)),
    PT_DST_End      As  Convert(SmallDateTime, DateAdd(Hour, -8, UTC_DST_End))
);
Go

Set DateFirst 7;
Go

;With Tally (Number) As
(
    Select  Row_Number() Over (Order By (Select Null)) - 1      Number
    From    (Values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))    a(n)
    Cross Join (Values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    Cross Join (Values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
),Cte(d,p) As 
(
    Select  Top (Year(GetDate()) - 2000 + 51)
            DateAdd(Year, Number, '20000101'),
            Case When Number < 7 Then 1 Else 0 End
    From    Tally 
    Order By Number
)
Insert  TZCalendar
        (Year, UTC_DST_Start, UTC_DST_End)
Select  Year(d),
        DateAdd(Hour, 7, DateAdd(Day, (7 - DatePart(WeekDay, DateAdd(Month, 2 + p, d)) + 1) % 7
            + (7 * Abs(p - 1)), DateAdd(Month, 2 + p, d))),
        DateAdd(Hour, 6, DateAdd(Day, (7 - DatePart(WeekDay, DateAdd(Month, 10, d)) + 1) % 7
            - (7 * p), DateAdd(Month, 10, d)))
From    Cte
Order By d;

Then check for a given date:

Select Case When Exists
(
    Select  *
    From    TZCalendar
    Where   @DateToCheck Between PT_DST_Start And PT_DST_End
)
Then 'Daylight Savings Time'
Else 'Standard Time'
End As IsDST

Keep in mind though, not all parts of the world utilize Daylight Savings Time.

SQLFiddle Demo