• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1117
  • Last Modified:

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

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
midfde
Asked:
midfde
  • 5
  • 3
  • 3
3 Solutions
 
Ephraim WangoyaCommented:
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
 
midfdeAuthor Commented:
Msg 8116, Level 16, State 1, Procedure dtUtc2Local, Line 6
Argument data type datetime is invalid for argument 2 of dateadd function.
0
 
Ephraim WangoyaCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
midfdeAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
midfdeAuthor Commented:
Do you mean the desribed function cannot be implemented in T-SQL at all, acperkins please?
0
 
Anthony PerkinsCommented:
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
 
Ephraim WangoyaCommented:

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
 
Anthony PerkinsCommented:
>>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
 
midfdeAuthor Commented:
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
 
midfdeAuthor Commented:
I found solution myself
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now