[ Determining if specific DateTime is in DST ]

Given a specific DateTime, and using just SQL, how can I determine if Daylight Savings Time will be in effect on that day? This is purely for "local" use, so I don't need to worry about it working on different environments or locales, just my own SQL Server 2008R2 server.

So, for example,

declare @myDate datetime='2 Jun 2014 14:00'

and then somehow evaluating @myDate would return a result that indicates "yes, this is in DST", where as

declare @myDate datetime='2 Dec 2014 14:00'

... would be "no, this isn't in DST".

The datetime specified will only be during working hours (8am-8pm), so I'm not too concerned about values that fall "in between" DST and non-DST. I'm in the UK, which goes between UTC and UTC+1.

Answer 1

Seems to be answered here:

Get Time zones in SQL Server 2008 R2

Essentially you need to load the Olson timezone database, or some subset of it, into your SQL server database and then you can access it using SQL.

Answer 2

I've cobbled together the following code, which makes the (British) assumptions that DST starts on the last Sunday in March, and ends in the last Sunday in October. It's fairly specific to my requirements, but seems to do the job - if anyone knows of a more elegant solution...

declare @myDate datetime='2 Jun 2014'

-- Declare October in this year
declare @october datetime=DATEADD(month,9, DATEADD(yy, DATEDIFF(yy,0,@myDate), 0))

-- Declare March in this year
declare @march datetime=DATEADD(month,3, DATEADD(yy, DATEDIFF(yy,0,@myDate), 0))

-- Find last Sunday in March
declare @DSTStart datetime=DATEADD(dd,
        -DATEPART(WEEKDAY,
                  DATEADD(dd, -DAY(DATEADD(mm, 1, @march)),
                          DATEADD(mm, 1, @march))) + 1,
        DATEADD(dd, -DAY(DATEADD(mm, 1, @march)), DATEADD(mm, 1, @march)))

-- Find last Sunday in October
declare @DSTEnd datetime= DATEADD(dd,
        -DATEPART(WEEKDAY,
                  DATEADD(dd, -DAY(DATEADD(mm, 1, @october)),
                          DATEADD(mm, 1, @october))) + 1,
        DATEADD(dd, -DAY(DATEADD(mm, 1, @october)), DATEADD(mm, 1, @october)))

if (@mydate BETWEEN @DSTStart and @DSTEnd) print 'DST'