Geeks With Blogs
Eron Wright - All Killer No Filler blog

You can convert local datetime values to UTC datetime values, and vice-versa, using the built-in GETUTCDATE() function:

DECLARE @LocalDate DATETIME
SET @LocalDate = GETDATE()

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

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

SELECT @LocalDate, @UTCDate, @LocalDate2

Note that GETUTCDATE() returns the current datetime in UTC.  By comparing the value with GETDATE() we can determine the time zone, which can then be used to adjust any date. 

I tried to bake these expressions into a set of user-defined functions, but SQL Server complained because user-defined functions cannot call non-deterministic functions (in this case GETDATE()/GETUTCDATE()).

Posted on Tuesday, September 14, 2004 11:35 PM Technology | Back to top


Comments on this post: UTC datetime values in SQL Server 2000

# re: UTC datetime values in SQL Server 2000
Requesting Gravatar...
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
Left by rajkumar on Mar 17, 2005 12:54 AM

# re: UTC datetime values in SQL Server 2000
Requesting Gravatar...
Please there are some mistakes in the given document.please check the same
bye
cp_raj@yahoo.com
Left by Rajkumar on Mar 17, 2005 12:55 AM

# re: UTC datetime values in SQL Server 2000
Requesting Gravatar...
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
Left by senthil kumar on Mar 23, 2005 5:47 AM

# re: UTC datetime values in SQL Server 2000
Requesting Gravatar...
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.
Left by Jonathan Cooper on Aug 15, 2005 10:24 AM

# re: UTC datetime values in SQL Server 2000
Requesting Gravatar...
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
Left by Bruce T on Feb 08, 2007 8:28 PM

# re: UTC datetime values in SQL Server 2000
Requesting Gravatar...
Thanks Bruce T - saved me some time.
Left by Phil U on Feb 27, 2007 5:16 PM

# re: UTC datetime values in SQL Server 2000
Requesting Gravatar...
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





Left by Justin Bennett on Mar 12, 2007 11:52 AM

# re: UTC datetime values in SQL Server 2000
Requesting Gravatar...
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
Left by Bruce T on May 10, 2007 12:01 AM

# re: UTC datetime values in SQL Server 2000
Requesting Gravatar...
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
Left by Steve Hiner on Sep 05, 2007 6:10 PM

# re: UTC datetime values in SQL Server 2000
Requesting Gravatar...
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
Left by glodson on Jun 02, 2008 3:03 PM

# re: UTC datetime values in SQL Server 2000
Requesting Gravatar...
Seriously, I love you guys...
Left by Sean on Nov 25, 2008 11:06 AM

# re: UTC datetime values in SQL Server 2000
Requesting Gravatar...
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.
Left by wintran on May 18, 2009 5:02 PM

# re: UTC datetime values in SQL Server 2000
Requesting Gravatar...
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
Left by BH on Aug 10, 2009 2:57 PM

# re: UTC datetime values in SQL Server 2000
Requesting Gravatar...
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.
Left by Kunal on Sep 17, 2009 2:36 AM

# re: UTC datetime values in SQL Server 2000
Requesting Gravatar...
Solluda Sollu
Left by sdfsdfsfsdfsdfs on Oct 06, 2010 1:02 PM

# re: UTC datetime values in SQL Server 2000
Requesting Gravatar...
DATEDIFF(Hour, GETDATE(), GETUTCDATE()), @UTCDate)
should be
DATEDIFF(Hour, GETUTCDATE, GETDATE() ()), @UTCDate)

to be suitable for DATEADD
Left by Jack Smith on Jul 28, 2011 11:35 PM

Your comment:
 (will show your gravatar)


Copyright © Eron Wright | Powered by: GeeksWithBlogs.net