Solved

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

Posted on 2011-02-11
11
1,097 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya 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:Ephraim Wangoya
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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
 
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:
Ephraim Wangoya 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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 to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

759 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