Solved

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

Posted on 2011-02-11
11
1,069 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

863 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

28 Experts available now in Live!

Get 1:1 Help Now