UFN_TIMEZONEENTRY_GETTIMEZONEOFFSETFROMUTC

CREATE function dbo.UFN_TIMEZONEENTRY_GETTIMEZONEOFFSETFROMUTC
(
    @TIMEZONEENTRYID uniqueidentifier,
    @DATEFOROFFSET datetime,
    @DATEISUTC bit
)
returns integer
with execute as caller
as begin
    /**
        If the date is UTC this function adds the base offset to the date to determine the
        time zone offset for that date.    
        This function does not handle ambiguous time or invalid time.        
        Ambiguous time is the hour before falling back (happens twice) and invalid time 
        is hour after springing forward (time never happens).
        In the ambiguous time case, it will always be considered in daylight savings time since
        the ambiguous time is before the cutoff (ie 1:45 AM first Sunday in November will always be
        daylight savings time even though we really don't know which time it really is).
        In the invalid time case, it will always be considered in daylight savings time since the
        invalid time is after the cutoff (ie 2:45 AM second Sunday in March will always be daylight 
        savings time even though this is not a valid time).
    **/
    declare @DAYLIGHTDELTA as integer
    declare @ISFIXEDSTARTDATE as bit
    declare @STARTFIXEDDATE as datetime
    declare @STARTMONTH as tinyint
    declare @STARTDAYOFWEEK as tinyint
    declare @STARTWEEKOFMONTH as tinyint
    declare @STARTDAYLIGHTDATE as datetime
    declare @STARTTIME as dbo.UDT_HOURMINUTE

    declare @ISFIXEDENDDATE as bit
    declare @ENDFIXEDDATE as datetime
    declare @ENDMONTH as tinyint
    declare @ENDDAYOFWEEK as tinyint
    declare @ENDWEEKOFMONTH as tinyint
    declare @ENDDAYLIGHTDATE as datetime
    declare @ENDTIME as dbo.UDT_HOURMINUTE
    declare @UTCOFFSET as integer

    select 
        @UTCOFFSET = TIMEZONEENTRY.BASETIMEZONEOFFSET
    from
        dbo.TIMEZONEENTRY
    where
        ID = @TIMEZONEENTRYID

    --if date is utc, then add base offset to more accurately determine

    --daylight savings time

    if @DATEISUTC = 1
        set @DATEFOROFFSET = dateadd(minute, @UTCOFFSET, @DATEFOROFFSET)

    select
        @DAYLIGHTDELTA = TZRULE.DAYLIGHTSAVINGSDELTA,
        --if month for date passed in is not the start month then

        --we don't need to determine exact start date

        @ISFIXEDSTARTDATE = (
        case
            when TZRULE.ISFIXEDSTARTDATE <> 0 then
                1
            when datepart(month, @DATEFOROFFSET) <> TZRULE.STARTMONTHCODE then
                1
            else
                0
        end),
        @STARTFIXEDDATE = TZRULE.STARTFIXEDDATE,
        @STARTMONTH = TZRULE.STARTMONTHCODE,
        @STARTDAYOFWEEK = TZRULE.STARTDAYOFWEEKCODE,
        @STARTWEEKOFMONTH = TZRULE.STARTWEEKOFMONTHCODE,
        @STARTTIME = TZRULE.STARTTIMEOFDAY,
        @ISFIXEDENDDATE = (
        case
            when TZRULE.ISFIXEDENDDATE <> 0 then
                1
            when datepart(month, @DATEFOROFFSET) <> TZRULE.ENDMONTHCODE then
                1
            else
                0
        end),
        @ENDFIXEDDATE = TZRULE.ENDFIXEDDATE,
        @ENDMONTH = TZRULE.ENDMONTHCODE,
        @ENDDAYOFWEEK = TZRULE.ENDDAYOFWEEKCODE,
        @ENDWEEKOFMONTH = TZRULE.ENDWEEKOFMONTHCODE,
        @ENDTIME = TZRULE.ENDTIMEOFDAY
    from
        dbo.TIMEZONEENTRYADJUSTMENTRULE TZRULE
    where
        TZRULE.TIMEZONEENTRYID = @TIMEZONEENTRYID and
        TZRULE.STARTRULEDATE <= @DATEFOROFFSET and
        TZRULE.ENDRULEDATE >= @DATEFOROFFSET

    if @ISFIXEDSTARTDATE = 0
        begin            
            set @STARTDAYLIGHTDATE = cast(datepart(year,@DATEFOROFFSET) as nvarchar(4)) + '-' + cast(@STARTMONTH as nvarchar(2)) + '-' + cast((7 * (@STARTWEEKOFMONTH - 1)) + 1 as nvarchar(2))

            while datepart(dw, @STARTDAYLIGHTDATE)<>@STARTDAYOFWEEK
                begin
                    set @STARTDAYLIGHTDATE = dateadd(d, 1, @STARTDAYLIGHTDATE)
                end

            set @STARTDAYLIGHTDATE = dateadd(hh, cast(substring(@STARTTIME, 1, 2) as integer), @STARTDAYLIGHTDATE)
            set @STARTDAYLIGHTDATE = dateadd(minute, cast(substring(@STARTTIME, 3, 2) as integer), @STARTDAYLIGHTDATE)

            --if week of month is 5, then this means last week of the month so if adding 5 weeks pushes

            --to next month, take the date back a week            

            if datepart(month, @STARTDAYLIGHTDATE) <> @STARTMONTH
                set @STARTDAYLIGHTDATE = dateadd(d, -7, @STARTDAYLIGHTDATE)    
        end
    else
        begin
            set @STARTDAYLIGHTDATE = dateadd(year, datepart(year, @DATEFOROFFSET)-1753, @STARTFIXEDDATE)
        end

    if @ISFIXEDENDDATE = 0
        begin
            set @ENDDAYLIGHTDATE = cast(datepart(year,@DATEFOROFFSET) as nvarchar(4)) + '-' + cast(@ENDMONTH as nvarchar(2)) + '-' + cast((7 * (@ENDWEEKOFMONTH - 1)) + 1 as nvarchar(2))

            while datepart(dw, @ENDDAYLIGHTDATE)<>@ENDDAYOFWEEK
                begin
                    set @ENDDAYLIGHTDATE = dateadd(d, 1, @ENDDAYLIGHTDATE)
                end

            set @ENDDAYLIGHTDATE = dateadd(hh, cast(substring(@ENDTIME, 1, 2) as integer), @ENDDAYLIGHTDATE)
            set @ENDDAYLIGHTDATE = dateadd(minute, cast(substring(@ENDTIME, 3, 2) as integer), @ENDDAYLIGHTDATE)

            --if week of month is 5, then this means last week of the month so if adding 5 weeks pushes

            --to next month, take the date back a week

            if datepart(month, @ENDDAYLIGHTDATE) <> @ENDMONTH
                set @ENDDAYLIGHTDATE = dateadd(d, -7, @ENDDAYLIGHTDATE)                
        end
    else
        begin
            set @ENDDAYLIGHTDATE = dateadd(year, datepart(year, @DATEFOROFFSET)-1753, @ENDFIXEDDATE)
        end

    if    @DATEFOROFFSET >= @STARTDAYLIGHTDATE and @DATEFOROFFSET < @ENDDAYLIGHTDATE
        set @UTCOFFSET = @UTCOFFSET + @DAYLIGHTDELTA

    return @UTCOFFSET

end