About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Tuesday, December 17, 2013

Getting the current Time from any Time Zone


Your application might be hosted in a particular location but accessed from many location, and when user of your application ask to retrieve date specific transaction that time the time zone will be considered by default as the time zone of Hosted server so user will be getting wrong date in this case.

This is a common problem which is faced by Developer to there are many way to tackle this issue. With the advent of SQL Server 2008 and the new data type TIMEOFFSET feature, this is bit of more easy to tackle this problem. Lets see how we can arrive to a solution with the new feature of SQL Server 2008

Creating Table TimeOffsets which will store Location Name and the TimeOffset (GMT) value for corresponding location. 

create table TimeOffsets
(Location varchar(100) primary key,
TimeOffset varchar(10))

Find the insert script at the end of this article.

Now here we are creating a function which will return a table with the current time of your location and the time for asked location.

This function is taking two argument @myplace and @targetplace for which it will give the current date for @myplace location and @targetplace location. It uses
  • Todatetimeoffset which will changes the current offset only  offset which we defined
  • Switchoffset :It does not only change the offset. It also changes the time with the appropriate time at Time zone specified in offset. I will take both this in deeper view in coming article.

  --create the function 
create function getgeographicaltime 
(@myplace as varchar(100),@targetplace as varchar(100)
)   
returns @timeatbothplace table(yourtime datetimeoffset,targettime datetimeoffset)
as 
begin  
      declare @targetplaceoffset varchar(100)  
      declare @myplaceoffset varchar(100)     
      declare @todaysdatetime datetime2  
      declare @todaysdatetimeoffset datetimeoffset     
      declare @returndatetimeoffset datetimeoffset     

      select @todaysdatetime = getdate()     

      select @myplaceoffset =
      [timeoffset] from timeoffsets where [location] = @myplace  
      select @targetplaceoffset =
      [timeoffset] from timeoffsets where [location] = @targetplace     

      select @todaysdatetimeoffset =
      todatetimeoffset (@todaysdatetime, @myplaceoffset)     
      select @returndatetimeoffset =
      switchoffset(@todaysdatetimeoffset,@targetplaceoffset)     

      insert into @timeatbothplace(yourtime,targettime)  
      select @todaysdatetimeoffset,@returndatetimeoffset;  

      return   
end

Lets now try to call this function to see our desired result, here we are passing @myplace as ‘DELHI’ and @targetplace as ‘LAS VEGAS’. Let’s fire this query to see the result.
  
Select * from GetGeographicalTime('Delhi','Las Vegas')
YourTime                           TargetTime
---------------------------------- ----------------------------------
2013-12-18 13:11:00.3300000 +05:30 2013-12-17 23:41:00.3300000 -08:00

Here we can see we got time for LAS Vegas corresponds to DELHI time zone. So when it is 2013-12-18 13:11:00 at Delhi, Vegas evidence 2013-12-17 23:41.

INSERT Script  for TimeOffset table

INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Addis Ababa', N'+03:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Adelaide', N'+09:30')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Algiers', N'+01:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Almaty', N'+06:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Amsterdam', N'+01:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Anchorage', N'-09:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Apia', N'-11:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Asunción', N'-04:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Athens', N'+02:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Auckland', N'+12:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Azores', N'-01:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Baghdad', N'+03:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Baku', N'+04:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Bangkok', N'+07:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Beijing', N'+08:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Berlin', N'+01:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Bogotá', N'-05:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Buenos Aires', N'-03:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Cairo', N'+02:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Calgary', N'-07:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Cape Town', N'+02:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Cape Verde', N'-01:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Casablanca', N'+00:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Chicago', N'-06:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Cocos Islands', N'+06:30')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Colombo', N'+05:30')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Dakar', N'+00:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Dallas', N'-06:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Darwin', N'+09:30')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Delhi', N'+05:30')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Denver', N'-07:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Dhaka', N'+06:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Dubai', N'+04:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Fernando de Noronha', N'-02:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Halifax', N'-04:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Hanoi', N'+07:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Havana', N'-05:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Helsinki', N'+02:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Hong Kong', N'+08:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Honolulu', N'-10:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Irkutsk', N'+08:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Jakarta', N'+07:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Jerusalem', N'+02:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Juneau', N'-09:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Karachi', N'+05:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Kathmandu', N'+05:45')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Kinshasa', N'+01:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Krasnoyarsk', N'+07:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Kuala Lumpur', N'+08:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Lagos', N'+01:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Las Vegas', N'-08:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Lima', N'-05:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Lisbon', N'+00:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'London', N'+00:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Los Angeles', N'-08:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Magadan', N'+11:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Maldives', N'+05:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Manila', N'+08:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Mauritius', N'+04:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Melbourne', N'+10:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Mexico City', N'-06:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Montevideo', N'-03:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Montreal', N'-05:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Moscow', N'+03:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Mumbai', N'+05:30')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Nairobi', N'+03:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'New York City', N'-05:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Nouméa', N'+11:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Omsk', N'+06:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Pago Pago', N'-11:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Papeete', N'-10:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Paris', N'+01:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Perth', N'+08:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Petropavlovsk-Kamchatsky', N'+12:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Phoenix', N'-07:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Pyongyang', N'+09:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Reykjavík', N'+00:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Rio de Janeiro', N'-03:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Rome', N'+01:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Saint Petersburg', N'+03:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Samara', N'+04:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'San Francisco', N'-08:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Santiago', N'-04:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'São Paulo', N'-03:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Seoul', N'+09:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'South Georgia and the South Sandwich Islands', N'-02:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Suva', N'+12:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Sydney', N'+10:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Tashkent', N'+05:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Tbilisi', N'+04:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Tehran', N'+03:30')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Tenerife', N'+00:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Tokyo', N'+09:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Toronto', N'-05:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Tunis', N'+01:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Vancouver', N'-08:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Vladivostok', N'+10:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Yakutsk', N'+09:00')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Yangon', N'+06:30')
INSERT [dbo].[TimeOffsets] ([Location], [TimeOffset])
VALUES (N'Yekaterinburg', N'+05:00')

Programmers jog with code and smile for no reason. Thats the beauty of programmer, love them ;) 

Post Reference: Vikram Aristocratic Elfin Share

Reference : http://blogs.msdn.com/b/manisblog/archive/2009/06/16/how-to-get-the-current-time-of-any-timezone-in-sql-server-2008.aspx
Posted by at
Labels: , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)