Getting the Current Time from Any Time Zone

Introduction

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

This is a common problem which is faced by developers too. There are many ways to tackle this issue. With the advent of SQL Server 2008 and the new data type TIMEOFFSET feature, this is bit more easy to tackle this problem. Let's see how we can arrive at a solution with the new feature of SQL Server 2008 Creating Table TimeOffsets which will store Location Name and the TimeOffset (GMT) value for the corresponding location.

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

Find the insert script at the end of this article.

• 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 of these in deeper view in the coming article.

  --create the function 
create function getgeographicaltime 
(@myplace as varchar(100),@targetplace as varchar(100)
)   
returns @timeatbothplace table(yourtime datetimeoffset,targettimedatetimeoffset)
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

Let's 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.

  --create the function 
create function getgeographicaltime 
(@myplace as varchar(100),@targetplace as varchar(100)
)   
returns @timeatbothplace table(yourtime datetimeoffset,targettimedatetimeoffset)
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 

Let's 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 that corresponds to DELHI time zone. So when it is 2013-12-18 13:11:00 at Delhi, Vegas evidence 2013-12-17 23:41.

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

This function takes two arguments, @myplace and @targetplace for which it will give the current date for @myplace location and @targetplace location. It uses

Programmers jog with code and smile for no reason. That's the beauty of programmers, love them ;)