sql timezone calculation
sql, sql-server, sql-server-2005, timezone
Solution
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.
Problem
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.