We create ASP.Net applications which is hosted at a Central Location say Seattle (USA) but the customers using the application are located all around the world and when they enter the transaction they want their local time to be considered but when we call the function GetDate() the SQL Server will take the server time into consideration. This is a classic problem & people have come up with various ways to tackle this problem but with SQL Server 2008 and DateTimeOffset feature it has become a bit easier.
So how to do it…. When the user registers herself to the ASP.NET application for the first time you can ask the Country and State of residence of this user which is stored in the database. Now, when the transaction is posted you can do the following to get the user’s local time of the transaction.
Thank you very much for visiting this article. In case if you are not on the MSDN blogs then I would request you to please visit my blog at http://blogs.msdn.com/manisblog because at times I improve the existing articles after reading emails from people who enthusiastically provide their feedback. These improvements might not be reflected on the other blog sites who have indexed this article.
I created a table called TimeOffsets with two Columns, Location and TimeOffset with a Primary Key on ‘Location’.
CREATE TABLE [dbo].[TimeOffsets](
) ON [PRIMARY]
I entered some values into this table. It is a little time consuming but it is a one time job. For your help I have attached an Excel Document along with this blog post so you can import the values into the SQL Server Table.
NOTE: The Time Zone Offset that I am providing below is not a complete list and it is not daylight saving aware. You have to make the changes accordingly. This is just a sample.
Fernando de Noronha
New York City
Rio de Janeiro
South Georgia and the South Sandwich Islands
Create a SQL Function that will return the value of type DateTimeOffset.
CREATE FUNCTION GetGeographicalTime
@MyPlace AS VARCHAR(100),
@TargetPlace AS VARCHAR(100)
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)
Now say suppose your Local TimeZone is “Delhi” (India) i.e. your server is located in Delhi and the local time zone is set for the server and you want to find out what is the current time in Los Angeles (USA) then you will call this function as follows:
Select dbo.GetGeographicalTime('Delhi','Los Angeles')
This will yield the current time at Los Angeles.
NOTE: I am attaching the SQL Script to create the Table and the Function as well as the Excel file containing the Time Zone Offsets along with this post. You can download it from here.
Thank you for reading this post … and keep a watch on my blog for newer posts.