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

CREATE FUNCTION [dbo].[convert_date_to_epoch](@param_date datetime)
RETURNS int
AS
BEGIN
/*  Author:  Thomas Bean
    Date:  11/01/2005
 
    This stored procedure will take in a string that represents a date.  The format
    of the string can be in any valid format that SQL Server 2K can convert into
    a datetime datatype.  Eg) mon dd yyyy, mm/dd/yy, mm/dd/yyyy,yy/mm/dd, etc.
    The output will be an integer conversion of the date given in into epoch
    format with the appropriate 5 or 6 hours added to compensate for CDT or CST.
    This function has been updated to account for the new U.S. Daylight Saving Time dates
    effective in 2007.
*/
 

/* Variable declarations */   
declare @date_in_int int,
        @date_in_date 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
 

select @date_in_date = CONVERT(datetime, @param_date)
 

/* Find out which days the time changed for the particular year */ 
 
select @year = YEAR(@date_in_date)
 
select @apr_01 = '04/01/' + convert(char(4), @year)
select @apr_02 = '04/02/' + convert(char(4), @year)
select @apr_03 = '04/03/' + convert(char(4), @year)
select @apr_04 = '04/04/' + convert(char(4), @year)
select @apr_05 = '04/05/' + convert(char(4), @year)
select @apr_06 = '04/06/' + convert(char(4), @year)
select @apr_07 = '04/07/' + convert(char(4), @year)
 
select @oct_25 = '10/25/' + convert(char(4), @year)
select @oct_26 = '10/26/' + convert(char(4), @year)
select @oct_27 = '10/27/' + convert(char(4), @year)
select @oct_28 = '10/28/' + convert(char(4), @year)
select @oct_29 = '10/29/' + convert(char(4), @year)
select @oct_30 = '10/30/' + convert(char(4), @year)
select @oct_31 = '10/31/' + convert(char(4), @year)

select @mar_08 = '03/08/' + convert(char(4), @year)
select @mar_09 = '03/09/' + convert(char(4), @year)
select @mar_10 = '03/10/' + convert(char(4), @year)
select @mar_11 = '03/11/' + convert(char(4), @year)
select @mar_12 = '03/12/' + convert(char(4), @year)
select @mar_13 = '03/13/' + convert(char(4), @year)
select @mar_14 = '03/14/' + convert(char(4), @year)

select @nov_01 = '11/01/' + convert(char(4), @year)
select @nov_02 = '11/02/' + convert(char(4), @year)
select @nov_03 = '11/03/' + convert(char(4), @year)
select @nov_04 = '11/04/' + convert(char(4), @year)
select @nov_05 = '11/05/' + convert(char(4), @year)
select @nov_06 = '11/06/' + convert(char(4), @year)
select @nov_07 = '11/07/' + convert(char(4), @year)

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

/* Convert the date parameter given into an epoch int */
 
select @date_in_int = DATEDIFF(second, '1970-01-01 00:00:00', @param_date)
 

/* Add the appropriate 5 or 6 hours depending on where the given
   date parameter falls on the calendar
*/
 
if (@date_in_date < @cdt)
begin
 select @date_in_int = @date_in_int + 21600
end
if ((@date_in_date >= @cdt) AND (@date_in_date < @cst))
begin
 select @date_in_int = @date_in_int + 18000
end
if (@date_in_date >= @cst)
begin
 select @date_in_int = @date_in_int + 21600
end
 
RETURN @date_in_int
 
END




