USE [ARSystem]
GO
/****** Object:  UserDefinedFunction [dbo].[convert_epoch_to_date]    Script Date: 10/02/2007 12:26:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[convert_epoch_to_date] (@unix_time INT)  
RETURNS DATETIME
AS  
BEGIN

/* Variable declarations */   
declare	@sql_datetime datetime,
        @year varchar(5),
        @apr_01 varchar(30),
        @apr_02 varchar(30),
        @apr_03 varchar(30),
        @apr_04 varchar(30),
        @apr_05 varchar(30),
        @apr_06 varchar(30),
        @apr_07 varchar(30),
        @oct_25 varchar(30),
	@oct_26 varchar(30),
        @oct_27 varchar(30),
        @oct_28 varchar(30),
        @oct_29 varchar(30),
        @oct_30 varchar(30),
        @oct_31 varchar(30),
        @mar_08 varchar(30),
        @mar_09 varchar(30),
	@mar_10 varchar(30),
        @mar_11 varchar(30),
        @mar_12 varchar(30),
        @mar_13 varchar(30),
        @mar_14 varchar(30),
        @nov_01 varchar(30),
        @nov_02 varchar(30),
        @nov_03 varchar(30),
        @nov_04 varchar(30),
        @nov_05 varchar(30),
        @nov_06 varchar(30),
        @nov_07 varchar(30),
	@cdt datetime,
	@cst datetime


	SET @sql_datetime = DATEADD(second, @unix_time,'19700101');
	
	select @year = YEAR(@sql_datetime)

	select @apr_01 = convert(char(4), @year) + '0401'
	select @apr_02 = convert(char(4), @year) + '0402'
	select @apr_03 = convert(char(4), @year) + '0403'
	select @apr_04 = convert(char(4), @year) + '0404'
	select @apr_05 = convert(char(4), @year) + '0405'
	select @apr_06 = convert(char(4), @year) + '0406'
	select @apr_07 = convert(char(4), @year) + '0407'
 
	select @oct_25 = convert(char(4), @year) + '1025'
	select @oct_26 = convert(char(4), @year) + '1026'
	select @oct_27 = convert(char(4), @year) + '1027'
	select @oct_28 = convert(char(4), @year) + '1028'
	select @oct_29 = convert(char(4), @year) + '1029'
	select @oct_30 = convert(char(4), @year) + '1030'
	select @oct_31 = convert(char(4), @year) + '1031'

	select @mar_08 = convert(char(4), @year) + '0308'
	select @mar_09 = convert(char(4), @year) + '0309'
	select @mar_10 = convert(char(4), @year) + '0310'
	select @mar_11 = convert(char(4), @year) + '0311'
	select @mar_12 = convert(char(4), @year) + '0312'
	select @mar_13 = convert(char(4), @year) + '0313'
	select @mar_14 = convert(char(4), @year) + '0314'

	select @nov_01 = convert(char(4), @year) + '1101'
	select @nov_02 = convert(char(4), @year) + '1102'
	select @nov_03 = convert(char(4), @year) + '1103'
	select @nov_04 = convert(char(4), @year) + '1104'
	select @nov_05 = convert(char(4), @year) + '1105'
	select @nov_06 = convert(char(4), @year) + '1106'
	select @nov_07 = convert(char(4), @year) + '1107'

if (@year < 2007)
begin

	/* Set the exact day for the Central Daylight Time change */ 

	if (DATEPART(weekday, CONVERT(datetime, @apr_01)) = 1)
	begin
	 select @cdt = CONVERT(datetime, @apr_01)
	end
	if (DATEPART(weekday, CONVERT(datetime, @apr_02)) = 1)
	begin
	 select @cdt = CONVERT(datetime, @apr_02)
	end
	if (DATEPART(weekday, CONVERT(datetime, @apr_03)) = 1)
	begin
	 select @cdt = CONVERT(datetime, @apr_03)
	end
	if (DATEPART(weekday, CONVERT(datetime, @apr_04)) = 1)
	begin
	 select @cdt = CONVERT(datetime, @apr_04)
	end
	if (DATEPART(weekday, CONVERT(datetime, @apr_05)) = 1)
	begin
	 select @cdt = CONVERT(datetime, @apr_05)
	end
	if (DATEPART(weekday, CONVERT(datetime, @apr_06)) = 1)
	begin
	 select @cdt = CONVERT(datetime, @apr_06)
	end
	if (DATEPART(weekday, CONVERT(datetime, @apr_07)) = 1)
	begin
	 select @cdt = CONVERT(datetime, @apr_07)
	end
	 
	/* Set the exact day for the Central Standard Time change */
	 
	if (DATEPART(weekday, CONVERT(datetime, @oct_25)) = 1)
	begin
	 select @cst = CONVERT(datetime, @oct_25)
	end
	if (DATEPART(weekday, CONVERT(datetime, @oct_26)) = 1)
	begin
	 select @cst = CONVERT(datetime, @oct_26)
	end
	if (DATEPART(weekday, CONVERT(datetime, @oct_27)) = 1)
	begin
	 select @cst = CONVERT(datetime, @oct_27)
	end
	if (DATEPART(weekday, CONVERT(datetime, @oct_28)) = 1)
	begin
	 select @cst = CONVERT(datetime, @oct_28)
	end
	if (DATEPART(weekday, CONVERT(datetime, @oct_29)) = 1)
	begin
	 select @cst = CONVERT(datetime, @oct_29)
	end
	if (DATEPART(weekday, CONVERT(datetime, @oct_30)) = 1)
	begin
	 select @cst = CONVERT(datetime, @oct_30)
	end
	if (DATEPART(weekday, CONVERT(datetime, @oct_31)) = 1)
	begin
	 select @cst = CONVERT(datetime, @oct_31)
	end

end
else
begin

	/* Set the exact day for the Central Daylight Time change */
	
	if (DATEPART(weekday, CONVERT(datetime, @mar_08)) = 1)
	begin
	 select @cdt = CONVERT(datetime, @mar_08)
	end
	if (DATEPART(weekday, CONVERT(datetime, @mar_09)) = 1)
	begin
	 select @cdt = CONVERT(datetime, @mar_09)
	end
	if (DATEPART(weekday, CONVERT(datetime, @mar_10)) = 1)
	begin
	 select @cdt = CONVERT(datetime, @mar_10)
	end
	if (DATEPART(weekday, CONVERT(datetime, @mar_11)) = 1)
	begin
	 select @cdt = CONVERT(datetime, @mar_11)
	end
	if (DATEPART(weekday, CONVERT(datetime, @mar_12)) = 1)
	begin
	 select @cdt = CONVERT(datetime, @mar_12)
	end
	if (DATEPART(weekday, CONVERT(datetime, @mar_13)) = 1)
	begin
	 select @cdt = CONVERT(datetime, @mar_13)
	end
	if (DATEPART(weekday, CONVERT(datetime, @mar_14)) = 1)
	begin
	 select @cdt = CONVERT(datetime, @mar_14)
	end
	 
	/* Set the exact day for the Central Standard Time change */
	 
	if (DATEPART(weekday, CONVERT(datetime, @nov_01)) = 1)
	begin
	 select @cst = CONVERT(datetime, @nov_01)
	end
	if (DATEPART(weekday, CONVERT(datetime, @nov_02)) = 1)
	begin
	 select @cst = CONVERT(datetime, @nov_02)
	end
	if (DATEPART(weekday, CONVERT(datetime, @nov_03)) = 1)
	begin
	 select @cst = CONVERT(datetime, @nov_03)
	end
	if (DATEPART(weekday, CONVERT(datetime, @nov_04)) = 1)
	begin
	 select @cst = CONVERT(datetime, @nov_04)
	end
	if (DATEPART(weekday, CONVERT(datetime, @nov_05)) = 1)
	begin
	 select @cst = CONVERT(datetime, @nov_05)
	end
	if (DATEPART(weekday, CONVERT(datetime, @nov_06)) = 1)
	begin
	 select @cst = CONVERT(datetime, @nov_06)
	end
	if (DATEPART(weekday, CONVERT(datetime, @nov_07)) = 1)
	begin
	 select @cst = CONVERT(datetime, @nov_07)
	end

end

/* Subtract the appropriate 5 or 6 hours depending on where the given
   date parameter falls on the calendar
*/

	if (@sql_datetime < @cdt)
	begin
	 select @sql_datetime = DATEADD(second, -21600,@sql_datetime)
	end
	if ((@sql_datetime >= @cdt) AND (@sql_datetime < @cst))
	begin
	 select @sql_datetime = DATEADD(second, -18000,@sql_datetime)
	end
	if (@sql_datetime >= @cst)
	begin
	 select @sql_datetime = DATEADD(second, -21600,@sql_datetime)
	end

	RETURN @sql_datetime;
END




