sql timezone calculation

Question

I have a table which stores the storecodes and their timezone. Now based on a given local date, I need to know if that date converted to stores local date was a in a weekend or not. Now I already know how to get the weekend part. I am struggling with the conversion. I am actually confused. My table has for example the following two values:

Store / TimeZone(Standard)
100 / 1 (This is frankfurt)
200 / 2 (This is tel aviv)

Our sql server is located in LA. I used the following code to get the UTC date:

DECLARE @LocalDate DATETIME, @UTCDate DATETIME
SET @LocalDate = GetDate()
-- convert local date to utc date
SET @UTCDate = DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), @LocalDate)

If I understood everything correct, I can now simply add the required hours to the @UTCDate to get the @UTCDate of that local timezone, correct?

For frankfurt it would be:

print DATEADD(HOUR, 1, @UTCDate)

Now this returns me the UTCDate for Frankfurt. How would I get the local date of Frankfurt though?

Edit: I am using Sql 2005.

Edit2: Complete example that is still confusing to me:

DECLARE @LocalDate DATETIME, @UTCDate DATETIME
SET @LocalDate = GetDate()
-- convert local date to utc date
SET @UTCDate = DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GetDate()), @LocalDate)
print GetDate()
print @UTCDate
print DATEADD(HOUR, 1, @UTCDate)

Output:

Jan 11 2010 12:32PM
Jan 11 2010  4:32AM
Jan 11 2010  5:32AM

Now does this mean, that if its 12:32PM in LA, then its 5:32AM in Franfurt? That seems be incorrect though. It should be 9:32PM in Franfurt.

Answer 1

You shouldn't start with local time. Start directly with UTC time:

DECLARE  @UTCDate DATETIME 
SET @UTCDate = GETUTCDATE();

Frankfurt is one hour ahead of UTC (UTC + 1) when summer time is not in effect so you add one hour:

print DATEADD(HOUR, 1, @UTCDate);

Remember that time zones are not on 60 minutes intervals, Mumbai is UTC + 5:30 and Nepal is UTC + 5:45. You must also account for daylight savings, and those change regularly. Argentine for instance opts to use the daylight on a year-by-year basis based on the ammount of water stored in its hydro power plants.

To sum up: always use UTC and leave the localisation of time to the client display and reporting.

Answer 2

If you have a UTCDate, it is the same for all timezones... I.e., when it's 1 am UTC in New York, it is also 1 am UTC in Frankfort. To get local time for any timnezone just add the offset (that's the value you have in your table) from the UTC DateTime... i.e., when it's 1 AM UTC, it's 2 am local in Frankfort. To remember whether to add or subtract, just remember that its always Earlier East.