January 10, 2016

Timezones

-- Timezone wrapper

Create function dbo.fn_TimeZones
(@StartDateIn datetime
,@EndDateIn  datetime
,@State            varchar(10)
)
returns numeric(17,2) as
/*
Timezone tables are updated with data for many decades so they will have to be undated once a century and are maintained by the Human Resources Department:
dbo.TimeZoneStates
dbo.TimeZoneOffset

(1) The day of the act, event, or default from which the designated time period begins to run is not included.
(2) If the last day of the designated time period is a Saturday, Sunday, Federal legal holiday (as enumerated in Rule 6(1) of the Federal Rules of Civil Procedure), or a day on which the reviewing entity is unable to conduct business in the usual manner, the deadline becomes the next day that is not one of the aforementioned days.

1) The start date is always the received date.
2) The start date is a weekday. Start counting on the immediate next week day.
3) When start counting, count 10 hours for each contiguous full business day.

--QA  (copy and paste into query window)
declare
@StartDate datetime
,@EndDate  datetime
)
-- define the business day parameters
set @StartDate = '2015-1-18  14:00:00.000'
set @EndDate = '2015-1-20   23:00:00.000'
*/
BEGIN
Declare
@StartDate datetime
,@EndDate datetime
-- Timezone offset parameters
,@DTbegin  datetime
,@DTend     datetime
,@STbegin   datetime
,@STend      datetime
,@ESToffset float
,@EDToffset float
,@TimeZoneST varchar(100)  --- make it big in case we expand to other timezones like Hawaiin Aleusian time
,@TimeZoneDT varchar(100)
,@BusDays     numeric(17,2)

SELECT @DTbegin
,@DTend  
,@STbegin
,@STend    
,@ESToffset
,@EDToffset
@TimeZoneST
@TimeZoneDT
FROM dbo.TimeZoneStates
WHERE [year] = datepart(year,@StartDateIn)
and [State] = @State

/* Timezone offset algorithms */
-- if the start date falls during daylight savings time use that offset
select @StartDateIn BETWEEN @DTbegin and @DTend
BEGIN
select @StartDate = DateADD(Hour,@EDToffset, @StartDateIn)
END

-- if the start date falls during standard time use that offset
select @StartDateIn BETWEEN @STbegin and @STend
BEGIN
select @StartDate = DateADD(Hour,@ESToffset, @StartDateIn)
END

-- if the end date falls during daylight savings time use that offset
select @EndDateIn BETWEEN @DTbegin and @DTend
BEGIN
select @EndDate = DateADD(Hour,@EDToffset, @EndDateIn)
END

-- if the end date falls during standard time use that offset
select @EndDateIn BETWEEN @STbegin and @STend
BEGIN
select @EndDate = DateADD(Hour,@ESToffset, @EndDateIn)
END

--<  Business hours/days are custom by company. Place your algorithms here. For example:

-- if StartDate falls before the first business hour, set it to the start hour of that day
select @StartDate = CASE

-- if StartDate falls before the last business hour, set it to the start hour of the next day
WHEN datepart(hour,@StartDate) <= @StartHour
THEN dateadd(hour,@StartHour,dateadd(day,1,convert(char(8),@StartDate,112)))

-- if StartDate falls after the last business hour, set it to the start hour of the next day
WHEN datepart(hour,@StartDate) >= @EndHour
THEN dateadd(hour,@StartHour,dateadd(day,2,convert(char(8),@StartDate,112)))

-- if StartDate falls within the 10 business hour, set it to the start hour of the next day
WHEN datepart(hour,@StartDate) <= @EndHour
THEN dateadd(hour,@StartHour,dateadd(day,1,convert(char(8),@StartDate,112)))

ELSE dateadd(hour,@StartDate,dateadd(day,1,convert(char(8),@StartDate,112)))
END
END
END
-- end of sample algorithms>

return @BusDays 
END