Function to calculate DST offset hour for given datetime

HOUR_OFFSET_FUNCTION.sql

ALTER FUNCTION [dbo].[UTC_TO_LOCAL_TIME_W_DST_ADJUST] (@DTTM DATETIME2    --  In UTC
)
RETURNS DATETIME2
AS
BEGIN
    --DECLARE @DTTM AS DATETIME2 = '01-Mar-2015 09:01:00';
	DECLARE @LOCAL AS DATETIME2;	
	DECLARE @DT_YEAR AS VARCHAR(4) = DATEPART(YEAR,@dttm);
	DECLARE @STARTDLS AS DATETIME2 = '01-Mar-' + @DT_YEAR;
	DECLARE @STOPDLS AS DATETIME2 = '01-Nov-' + @DT_YEAR;
	DECLARE @DW AS INT = DATEPART(DW, @STARTDLS);	

	IF @DW = 1
	BEGIN
		SET @STARTDLS = CAST(DATEADD(DD, 7, @STARTDLS) AS DATETIME2);
	     SET @STOPDLS = CAST(DATEADD(DD, 0, @STOPDLS) AS DATETIME2);
	END;
	ELSE
	BEGIN
		SET @STARTDLS = CAST(DATEADD(DD, 15 - @DW, @STARTDLS) AS DATETIME2);
		SET @STOPDLS = CAST(DATEADD(DD, 8 - @DW, @STOPDLS) AS DATETIME2);
	END;

	/* We would use the below if the time coming in as a parameter was local time, but since it's UTC, we'll use what 2am translated to UTC 
	SET @STARTDLS = CAST(DATEADD(HH, 2, @STARTDLS) AS DATETIME2)
	SET @STOPDLS = CAST(DATEADD(HH, 2, @STOPDLS) AS DATETIME2)

	Instead:

     March 8th, 2015 2am PST = Mar 8th, 2015 10am UTC
     November 1, 2015 2am PST (2am AS OF PDT (1am PST) =  Nov 1, 2015 9am UTC
	*/

	SET @STARTDLS = CAST(DATEADD(HH, 10, @STARTDLS) AS DATETIME2);
	SET @STOPDLS = CAST(DATEADD(HH, 9, @STOPDLS) AS DATETIME2);


	IF @DTTM between @STARTDLS and @STOPDLS
	BEGIN  --  @dt is within Daylight Savings Time Rules - Add an hour
		SET @LOCAL = DATEADD(HH, -7, @DTTM);
	END;
	ELSE    --  @dt is outside Daylight Savings Time Rules - No Adjustment
	BEGIN
		SET @LOCAL = DATEADD(HH, -8, @DTTM);
	END;
	RETURN @LOCAL;

END;

Here's a tally table version:

set nocount on;

if object_id('dbo.Tally') is not null drop table dbo.tally

go

select top 10000 identity(int,1,1) as ID

into dbo.Tally from master.dbo.SysColumns

alter table dbo.Tally

add constraint cix_tally_ID primary key clustered(ID)

go

select * from dbo.Tally

declare
@MarchFirst date = '3/1/2015',
@marchThirtyFirst date = '3/31/2015',
@NovemberFirst date = '11/1/2015',
@novemberThirtieth date = '11/30/2015';

with marchSunday as(
select dateadd(DD,ID-1,@MarchFirst) as [DATE],

day(dateadd(DD,ID-1,@MarchFirst)) as [DAY],

month(dateadd(DD,ID-1,@MarchFirst)) as [MONTH],

year(dateadd(DD,ID-1,@MarchFirst)) as [YEAR],
DATENAME(dw, dateadd(DD,ID-1,@MarchFirst)) dayoftheweek,
rowNum = row_number() over (order by id)
from dbo.Tally

where dateadd(DD,ID-1,@MarchFirst)<=@marchThirtyFirst
and
DATENAME(dw, dateadd(DD,ID-1,@MarchFirst)) ='sunday'
)

select * from marchSunday where rowNum = 2;

with novemberSunday as(
select dateadd(DD,ID-1,@NovemberFirst) as [DATE],

day(dateadd(DD,ID-1,@NovemberFirst)) as [DAY],

month(dateadd(DD,ID-1,@NovemberFirst)) as [MONTH],

year(dateadd(DD,ID-1,@NovemberFirst)) as [YEAR],
DATENAME(dw, dateadd(DD,ID-1,@NovemberFirst)) dayoftheweek,
rowNum = row_number() over (order by id)
from dbo.Tally

where dateadd(DD,ID-1,@NovemberFirst)<=@novemberThirtieth
and
DATENAME(dw, dateadd(DD,ID-1,@NovemberFirst)) ='sunday'
)

select * from novemberSunday where rowNum = 2;

/*
It's kinda rough like this in terms of what you're trying to get but with a few tweaks you could just pass in your date to see if it is between the two second Sundays of March and November. Not saying this is better, just another way of doing it.
*/