Solved

UTC to Local Time conversion in Microsoft SQL Server 2008 question.

Posted on 2011-02-11
11
1,055 Views
Last Modified: 2012-05-11
Please help me to fill the dots below.
create function dtUtc2Local(@dtUtc datetime)returns datetime as
--Parameter: UTC datetime
--returns: Corresponding local time
begin
Declare @dtLocal as datetime
...
SELECT @dtLocal = ...
return @dtLocal
END
go
Is you proposed solution daylight-saving-aware please?
MS Windows OS.
Thanks.
0
Comment
Question by:midfde
  • 5
  • 3
  • 3
11 Comments
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 333 total points
ID: 34873741
try


create function dtUtc2Local(@dtUtc datetime)returns datetime as
--Parameter: UTC datetime 
--returns: Corresponding local time
begin
Declare @dtLocal as datetime
SELECT @dtLocal = DATEADD(HOUR, 
                  DATEDIFF(HOUR, GETUTCDATE(), GETDATE()),
		          DATEADD(SECOND, @dtUtc, '1/1/1970 12:00 AM'))
return @dtLocal
END

Open in new window

0
 
LVL 1

Author Comment

by:midfde
ID: 34873853
Msg 8116, Level 16, State 1, Procedure dtUtc2Local, Line 6
Argument data type datetime is invalid for argument 2 of dateadd function.
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34874016
Sorry, thats no correct
Try
create function dtUtc2Local(@dtUtc datetime)returns datetime as
--Parameter: UTC datetime 
--returns: Corresponding local time
begin
  DECLARE @TimeDiff int
  Declare @dtLocal as datetime
  --get time difference
  SET @TimeDiff = DATEDIFF(hh, GETUTCDATE(), GETDATE())
  SELECT @dtLocal = DATEADD(HH, @TimeDiff, @dtUtc)
  return @dtLocal
END

Open in new window

0
 
LVL 1

Author Comment

by:midfde
ID: 34874079
The code returns the same "-5" time offset for both within and outsode daytime saving period.
It should be "-5" for winter (EST) and "-4" for summer (EDT) here in Maryland, USA.
-----------------------
2010-12-31 19:00:00.000
(1 row(s) affected)
-----------------------
2011-06-30 19:00:00.000
(1 row(s) affected)
begin tran 
go
create function dtUtc2Local(@dtUtc datetime)returns datetime as
--Parameter: UTC datetime 
--returns: Corresponding local time
begin
  DECLARE @TimeDiff int
  Declare @dtLocal as datetime
  --get time difference
  SET @TimeDiff = DATEDIFF(hh, GETUTCDATE(), GETDATE())
  SELECT @dtLocal = DATEADD(HH, @TimeDiff, @dtUtc)
  return @dtLocal
END 

go 
select dbo.dtUtc2Local('1/1/2011 12:00 am')
select dbo.dtUtc2Local('7/1/2011 12:00 am')
rollback

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34876603
You are going to have to write a .NET app to get the offset. Also, keep in mind that there are timezones that are 30 minutes apart and not a full hour.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:midfde
ID: 34876701
Do you mean the desribed function cannot be implemented in T-SQL at all, acperkins please?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34876807
It can be, it is just that as you have discovered you will need to adjust for DST.  I have never done this before, but perhaps you could do this if you had a table of dates dating back to the first entry that shows whether it is DST date or not.  This may seem at first overkill, but consider that doing a lookup on a table is far simpler and something that SQL Server does rather well, compared to trying to calculate whether that particular date for that year was DST or not.  If you go back say 5 years that is less than 2000 rows of data.
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 333 total points
ID: 34880109

Calculating whether a date is within daylight savings is not intensive by any standards. and even if you wanted to use a table of dates, why would you need 2000 records for 5 years?
You would only need to store the first and last dates of daylight savings for any given year.

I'm currently stretched so I'm not able to do this right now but I would be glad to show you how
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 167 total points
ID: 34880299
>>would you need 2000 records for 5 years?<<
I have already stated why, but I guess you did not read my full comment.  Let me give you the abbreviated version:
It is trivial doing it that way.
0
 
LVL 1

Author Comment

by:midfde
ID: 34880529
I wrote a function to convert UTC time to our local Estern Time zone.
What I want is to convert it to whatever time zone that is stored in the client's OS, say in Alaska (pronouns instead of literals wanted).
begin tran 
go

create function dtUtc2EasternTime(
			@dtUtc datetime
	   )returns datetime as
--Parameter: @dtUtc - UTC datetime 
--returns: Corresponding local time
begin
  Declare @dtLocal as datetime =
  dateadd(hh,
  case when 
		@dtUtc between convert(datetimeoffset,'03/11/2007 2:00 am -05:00') and convert(datetimeoffset,'11/04/2007 2:00 am -05:00') or
		@dtUtc between convert(datetimeoffset,'03/09/2008 2:00 am -05:00') and convert(datetimeoffset,'11/02/2008 2:00 am -05:00') or
		@dtUtc between convert(datetimeoffset,'03/08/2009 2:00 am -05:00') and convert(datetimeoffset,'11/01/2009 2:00 am -05:00') or
		@dtUtc between convert(datetimeoffset,'03/14/2010 2:00 am -05:00') and convert(datetimeoffset,'11/07/2010 2:00 am -05:00') or
		@dtUtc between convert(datetimeoffset,'03/13/2011 2:00 am -05:00') and convert(datetimeoffset,'11/06/2011 2:00 am -05:00') or
		@dtUtc between convert(datetimeoffset,'03/11/2012 2:00 am -05:00') and convert(datetimeoffset,'11/04/2012 2:00 am -05:00') or
		@dtUtc between convert(datetimeoffset,'03/10/2013 2:00 am -05:00') and convert(datetimeoffset,'11/03/2013 2:00 am -05:00') or
		@dtUtc between convert(datetimeoffset,'03/09/2014 2:00 am -05:00') and convert(datetimeoffset,'11/02/2014 2:00 am -05:00') or
		@dtUtc between convert(datetimeoffset,'03/08/2015 2:00 am -05:00') and convert(datetimeoffset,'11/01/2015 2:00 am -05:00') 
	then -4 
	else case when datepart(yyyy,@dtUtc) between 2007 and 2015 
			  then -5 
			  else datediff(hh,GetUtcDate(),GetDate()) 
		 end 
 end, @dtUtc)
  return @dtLocal
END 

go 
select dbo.dtUtc2EasternTime('1/1/2010 12:00 am')
select dbo.dtUtc2EasternTime('7/1/2010 12:00 am')
select dbo.dtUtc2EasternTime('1/1/2011 12:00 am')
select dbo.dtUtc2EasternTime('7/1/2011 12:00 am')
rollback

END 

go 
select dbo.dtUtc2EasternTime('1/1/2010 12:00 am')
select dbo.dtUtc2EasternTime('7/1/2010 12:00 am')
select dbo.dtUtc2EasternTime('1/1/2011 12:00 am')
select dbo.dtUtc2EasternTime('7/1/2011 12:00 am')
rollback

Open in new window

0
 
LVL 1

Author Closing Comment

by:midfde
ID: 34961211
I found solution myself
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now