/* Input Coordinated Universal Time (UTC) DATETIME, desired timezone and 1 to enable DST - Example: print dbo.UTCtoLocalDate('3/11/2007 12:00:00', -8, 1) for PST
Example: print dbo.UTCtoLocalDate ('3/11/2007 12:00:00', -3.5, 1) for NST*/

CREATE FUNCTION [dbo].[UTCtoLocalDate] (@UTCDate DATETIME, @TZ NUMERIC(3,1), @DST INT)
RETURNS DATETIME AS
BEGIN
/* If the timzone does not already equal GMT 0 */
IF ( @TZ <> 0 ) or (@DST = 1)
BEGIN
DECLARE @LocalDate DATETIME
DECLARE @UTCDelta NUMERIC(5,1)
DECLARE @thisYear INT
DECLARE @DSTDay INT
DECLARE @NormalDay INT
DECLARE @DSTDate DATETIME
DECLARE @NormalDate DATETIME

SET @thisYear = CONVERT(INT, DATEPART(yyyy, @UTCDate))

IF (@thisYear < 2007 )
/* DST for prior 2007 */
BEGIN
SET @DSTDay = ( 2 + 6 * @thisYear - FLOOR(@thisYear / 4) ) % 7 + 1
SET @NormalDay = ( 31 - ( FLOOR( @thisYear * 5 / 4) + 1) % 7)

SET @DSTDate = CONVERT(DATETIME, '4/' + CONVERT(varchar(2), @DSTDay) + '/' + CONVERT(varchar(4), @thisYear) + ' 2:00:00.000 AM' )
SET @NormalDate = CONVERT(DATETIME, '10/' + CONVERT(varchar(2), @NormalDay) + '/' + CONVERT(varchar(4), @thisYear) + ' 2:00:00.000 AM' )
END
ELSE
/* DST for 2007 and after */
BEGIN
SET @DSTDay = ( 14 - ( FLOOR( 1 + @thisYear * 5 / 4 ) ) % 7 )
SET @NormalDay = ( 7 - ( FLOOR ( 1 + @thisYear * 5 / 4) ) % 7 )

SET @DSTDate = CONVERT(DATETIME, '3/' + CONVERT(varchar(2), @DSTDay) + '/' + CONVERT(varchar(4), @thisYear) + ' 2:00:00.000 AM' )
SET @NormalDate = CONVERT(DATETIME, '11/' + CONVERT(varchar(2), @NormalDay) + '/' + CONVERT(varchar(4), @thisYear) + ' 2:00:00.000 AM' )
END

/* Adjust timezone if in DST Period */
IF ((@UTCDate > @DSTDate) AND (@UTCDate < @NormalDate))
BEGIN
SET @UTCDelta = (@TZ + 1) * 60
END
/* Adjust timezone without DST */
ELSE
BEGIN
SET @UTCDelta = @TZ * 60
END

/* now CONVERT UTC date to local date */
SET @LocalDate = DATEADD(mi, @UTCDelta, @UTCDate)
END
ELSE
/* If the timzone already equals GMT 0 without DST */
BEGIN
SET @LocalDate = @UTCDate
END

RETURN(@LocalDate)
END