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.


Step 1:

I created a table called TimeOffsets with two Columns, Location and TimeOffset with a Primary Key on ‘Location’.

CREATE TABLE [dbo].[TimeOffsets](

 

      [Location] [varchar](100) NOT NULL,

 

      [TimeOffset] [varchar](10) NOT NULL,

 

 CONSTRAINT [PK_TimeOffsets] PRIMARY KEY CLUSTERED

 (

       [Location] ASC

 

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

 ) ON [PRIMARY]

 

Step 2:

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.

Location

TimeOffset

Addis Ababa

+03:00

Adelaide

+09:30

Algiers

+01:00

Almaty

+06:00

Amsterdam

+01:00

Anchorage

-09:00

Apia

-11:00

Asunción

-04:00

Athens

+02:00

Auckland

+12:00

Azores

-01:00

Baghdad

+03:00

Baku

+04:00

Bangkok

+07:00

Beijing

+08:00

Berlin

+01:00

Bogotá

-05:00

Buenos Aires

-03:00

Cairo

+02:00

Calgary

-07:00

Cape Town

+02:00

Cape Verde

-01:00

Casablanca

+00:00

Chicago

-06:00

Cocos Islands

+06:30

Colombo

+05:30

Dakar

+00:00

Dallas

-06:00

Darwin

+09:30

Delhi

+05:30

Denver

-07:00

Dhaka

+06:00

Dubai

+04:00

Fernando de Noronha

-02:00

Halifax

-04:00

Hanoi

+07:00

Havana

-05:00

Helsinki

+02:00

Hong Kong

+08:00

Honolulu

-10:00

Irkutsk

+08:00

Jakarta

+07:00

Jerusalem

+02:00

Juneau

-09:00

Karachi

+05:00

Kathmandu

+05:45

Kinshasa

+01:00

Krasnoyarsk

+07:00

Kuala Lumpur

+08:00

Lagos

+01:00

Las Vegas

-08:00

Lima

-05:00

Lisbon

+00:00

London

+00:00

Los Angeles

-08:00

Magadan

+11:00

Maldives

+05:00

Manila

+08:00

Mauritius

+04:00

Melbourne

+10:00

Mexico City

-06:00

Montevideo

-03:00

Montreal

-05:00

Moscow

+03:00

Mumbai

+05:30

Nairobi

+03:00

New York City

-05:00

Nouméa

+11:00

Omsk

+06:00

Pago Pago

-11:00

Papeete

-10:00

Paris

+01:00

Perth

+08:00

Petropavlovsk-Kamchatsky

+12:00

Phoenix

-07:00

Pyongyang

+09:00

Reykjavík

+00:00

Rio de Janeiro

-03:00

Rome

+01:00

Saint Petersburg

+03:00

Samara

+04:00

San Francisco

-08:00

Santiago

-04:00

São Paulo

-03:00

Seoul

+09:00

South Georgia and the South Sandwich Islands

-02:00

Suva

+12:00

Sydney

+10:00

Tashkent

+05:00

Tbilisi

+04:00

Tehran

+03:30

Tenerife

+00:00

Tokyo

+09:00

Toronto

-05:00

Tunis

+01:00

Vancouver

-08:00

Vladivostok

+10:00

Yakutsk

+09:00

Yangon

+06:30

Yekaterinburg

+05:00

 

 

Step 3:

Create a SQL Function that will return the value of type DateTimeOffset.

CREATE FUNCTION GetGeographicalTime

(

      @MyPlace AS VARCHAR(100),

      @TargetPlace AS VARCHAR(100)

)

RETURNS 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)

      RETURN @returnDateTimeOffset

END

 

 

Step 4:

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.