Eron Wright - All Killer No Filler

# re: UTC datetime values in SQL Server 2000

Googled sql convert utc local and came up with your post.

I have a problem with the way that utc to local is calculated. The datediff param should be reversed
SET @LocalDate2 = DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), @UTCDate)

The way to get around the deterministic function limitation is to pass in getdate() and getUTCdate() in the UDF.

createfunction utc2local(@utc2convert datetime,@localnow datetime, @utcnow datetime) returns datetime as
return DATEADD(Hour, DATEDIFF(Hour, @localnow, @utcnow), @utc2convert)
go
12/22/2004 9:43 AM | Mike

# re: UTC datetime values in SQL Server 2000

DECLARE @LocalDate DATETIME
SET @LocalDate = GETDATE()

-- convert local date to utc date
DECLARE @UTCDate DATETIME,@dtDate int
Set @dtDate=DATEDIFF(millisecond,GETDATE(),GETUTCDATE())
SET @UTCDate = DATEADD(millisecond, DATEDIFF(millisecond,GETDATE(),GETUTCDATE()), @LocalDate)
select @dtDate

-- convert utc date to local date
DECLARE @LocalDate2 DATETIME
SET @LocalDate2 = DATEADD(millisecond, DATEDIFF(millisecond,GETUTCDATE(),GETDATE()), @UTCDate)


SELECT @LocalDate, @UTCDate, @LocalDate2 3/17/2005 12:54 AM | rajkumar

# re: UTC datetime values in SQL Server 2000

Please there are some mistakes in the given document.please check the same
bye
cp_raj@yahoo.com 3/17/2005 12:55 AM | Rajkumar

# re: UTC datetime values in SQL Server 2000

i put
Select datediff(mi,'23:00','2:00')
in sql 2000 it will return
-1260
but i need different minute from the two argument of the above parameter what can i do
3/23/2005 5:47 AM | senthil kumar

# re: UTC datetime values in SQL Server 2000

I thought about this, but it won't always work.
Say it's currently summer and i'm in daylight savings time, my difference will include DST. Now i try to convert a date from 6 months ago, i'll be an hour out. 8/15/2005 10:24 AM | Jonathan Cooper

# re: UTC datetime values in SQL Server 2000

Here is a way for all date in the New US DST for 2007 forward also

CREATE FUNCTION [dbo].[UTCtoLocalDate] (@UTCDate DATETIME, @TZ INT)
RETURNS DATETIME AS
BEGIN
IF ( DATEPART(hh, @UTCDate) <> 0 )
BEGIN
DECLARE @LocalDate DATETIME
DECLARE @UTCDelta INT
DECLARE @thisYear INT
DECLARE @DSTDay INT
DECLARE @NormalDay INT
DECLARE @DSTDate DATETIME
DECLARE @NormalDate DATETIME

SET @thisYear = YEAR(@UTCDate)

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

SET @DSTDate = '4/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'
SET @NormalDate = '10/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'
END
ELSE
BEGIN
SET @DSTDay = ( 14 - ( FLOOR( 1 + @thisYear * 5 / 4 ) ) % 7 )
SET @NormalDay = ( 7 - ( FLOOR ( 1 + @thisYear * 5 / 4) ) % 7 )

SET @DSTDate = '3/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'
SET @NormalDate = '11/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'
END

IF ((@UTCDate > @DSTDate) AND (@UTCDate < @NormalDate))
BEGIN
SET @UTCDelta = @TZ + 1
END
ELSE
BEGIN
SET @UTCDelta = @TZ
END
-- now convert utc date to local date
SET @LocalDate = DATEADD(Hour, @UTCDelta, @UTCDate)
END
ELSE
BEGIN
SET @LocalDate = @UTCDate
END
RETURN(@LocalDate)
END
GO
2/8/2007 8:28 PM | Bruce T

# re: UTC datetime values in SQL Server 2000

Thanks Bruce T - saved me some time. 2/27/2007 5:16 PM | Phil U

# re: UTC datetime values in SQL Server 2000

Hi Bruce,
I monkeyed with your code and switched it around a bit. I think the first IF statement you meant to evaluate if the time zone passed to it was <> 0 and not the hour of the UTCDate. I was scratching my head for hours trying to figure out what I was doing wrong.

Here you go and thanks!

/*Bruce T Change from UTC TimeZone SQL Function
Modified by Justin Bennett
http://geekswithblogs.net/ewright/archive/2004/09/14/11180.aspx */

/* 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





3/12/2007 11:52 AM | Justin Bennett

# re: UTC datetime values in SQL Server 2000

Justin,
You are correct, the database I wrote it for stored both date, and datetime calendar events, and if it stored date only (a midnight hour time) like 5/9/2007 12:00:00.000 AM the UTC need not apply - sorry I should have documented my code better -- the IF ( DATEPART(hh, @UTCDate) &lt;&gt; 0 ) was a crude way to capure that.

Thanks for fixing it

Bruce T 5/10/2007 12:01 AM | Bruce T

# re: UTC datetime values in SQL Server 2000

This is great... except that as far as I can tell there's no way to turn off DST adjustments. The function has an arguement that presumably is supposed to bypass DST for those of us in Arizona however it isn't checked in the function except when the TZ is also 0. I fixed it so it works for non-DST areas as well plus it should be a lot faster since it has way less work to do.

/*Bruce T Change from UTC TimeZone SQL Function
Modified by Justin Bennett
Modified by Steve Hiner
http://geekswithblogs.net/ewright/archive/2004/09/14/11180.aspx */

/* 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)

IF (@DST = 1)
BEGIN
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

IF ((@UTCDate > @DSTDate) AND (@UTCDate < @NormalDate))
/* Adjust timezone if in DST Period */
BEGIN
SET @UTCDelta = (@TZ + 1) * 60
END
ELSE
/* Adjust timezone without DST */
BEGIN
SET @UTCDelta = @TZ * 60
END
END
ELSE
/* If no DST adjustment required */
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
9/5/2007 6:10 PM | Steve Hiner

# re: UTC datetime values in SQL Server 2000

There was a big mistake in what you have written about time conversion. I have corrected your scripts - now they are OK!!! :)))

DECLARE @LocalDate DATETIME
SET @LocalDate = GETDATE()

-- convert local date to utc date
DECLARE @UTCDate DATETIME
SET @UTCDate = DATEADD(Hour, DATEDIFF(Hour,GETDATE(),GETUTCDATE()), @LocalDate)

-- convert utc date to local date
DECLARE @LocalDate2 DATETIME
SET @LocalDate2 = DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), @UTCDate)

SELECT @LocalDate, @UTCDate, @LocalDate2 6/2/2008 3:03 PM | glodson

# re: UTC datetime values in SQL Server 2000

Seriously, I love you guys... 11/25/2008 11:06 AM | Sean

# re: UTC datetime values in SQL Server 2000

Final UTCToLocalDate() convert is not exactly accurate on the day the daylight savings occurs.

Specifically, the funciton compares against the UTCDate, which it should compare the daylight savings off of the calculated local time.

To fix,
1. Adjust for the local TimeZone.
2. Compare that value against the Daylight saving switch dates.

This is only important if you will be utilizing this function for payroll calculates on the days the clock switches. 5/18/2009 5:02 PM | wintran

# re: UTC datetime values in SQL Server 2000

Hi,

This function is great. How do I to the reverse, ie convert a local date to UTC date?

Thank you for your help,
Brave 8/10/2009 2:57 PM | BH

# re: UTC datetime values in SQL Server 2000

I am having '1252580454296' as unixtime stored in my table.
and there may be many more enteries in table for the same day.
i want to write a SQL make a group of records for each hour of a particular day.

example: suppose i am having 20 records of 09/17/2009 .now i want to fragment this 09/17/2009 in 24 hours i.e 24 rows should appear.

anyone can help me out. 9/17/2009 2:36 AM | Kunal