USE [DATI]
GO
/****** Object:  UserDefinedFunction [dbo].[UTCToLocal]    Script Date: 11/05/2010 14:39:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[UTCToLocal](@UTCDate datetime)
returns datetime
as
begin
declare @LocalTime datetime, @Offset int
DECLARE @year int
DECLARE @MarDate datetime
DECLARE @OctDate datetime
DECLARE @DST_Start datetime
DECLARE @DST_End datetime
DECLARE @Date_Converted datetime

set @LocalTime=@UTCDate

SET @year = DATEPART(year, @LocalTime)
SET @MarDate = '03-15-' + CONVERT(char(4), @year) 
SET @OctDate = '10-15-' + CONVERT(char(4), @year) 
SET @DST_Start = DATEADD(hour, 1, (dbo.udf_LastSundayOfTheMonth(@MarDate)))
SET @DST_End = DATEADD(hour, 1, (dbo.udf_LastSundayOfTheMonth(@OctDate)))
SET @DST_End = DATEADD(second, -1, @DST_End)

if @LocalTime between @DST_Start and @DST_end
set @Offset=+2
else
set @Offset=+1

set @Date_Converted=DATEADD(hh,@offset,@LocalTime)

return @Date_Converted
end

USE [DATI]
GO
/****** Object:  UserDefinedFunction [dbo].[udf_LastSundayOfTheMonth]    Script Date: 11/05/2010 14:39:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[udf_LastSundayOfTheMonth]
( @Date datetime )
RETURNS datetime
AS
BEGIN
DECLARE @weekday int
DECLARE @Lastday datetime
DECLARE @number int
DECLARE @day datetime
SELECT @weekday = 0
SELECT @Lastday = (DATEADD(day, -1, CAST(STR(MONTH(@Date)+1) + '/' + STR(01) + '/' + STR(YEAR(@Date)) AS DateTime)))
SELECT @number = DATEPART(day, @Lastday)
WHILE @weekday <> 1
BEGIN
SELECT @day = (CAST(STR(MONTH(@Date)) + '/' + STR(@number) + '/' + STR(YEAR(@Date)) AS DateTime))
SELECT @weekday = DATEPART(weekday, @day)
SELECT @number = @number - 1
END
RETURN @day
END