Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-02-11
11
Medium Priority
?
1,112 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:Ephraim Wangoya
Ephraim Wangoya earned 666 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 666 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 334 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

927 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