Getting the time zone

By Bill Graziano on 20 July 2000 | Tags: Application Design

Kristof writes "GETDATE() returns the current local date/time. There doesn't seem to be any function to get this in GMT time, or even find out what time zone the SQL server is running in. Assuming the client connects to the server with ODBC and doesn't know what timezone the server is running in, how can it find out? I know the client could subtract the clients time from the servers GETDATE(), but that would only work if the two machine's clocks are synchronized"

I've actually got four answers for this question. You can pick which to implement depending on how much work you want to do and how important this feature is. The basic problem is that SQL Server can't easily tell you what time zone it's in.

If you have a Target Server configured to accept SQL Server Agent jobs you can find out the time zone difference between the two servers. You will need to write some type of script to access the TimeZoneAdjustment property of the TargetServer. Probably not what you're looking for.

Second, you can compare the times on the client and server and round to the nearest hour. As long as the clocks are within 30 minutes of the correct time this will give you pretty good results. Kind of "kludgy" though and you mentioned you didn't like this solution.

Third, you can create a configuration table inside SQL Server and store the number of hours difference from GMT. This should be a pretty static value and you can easily configure it per server. It should work well unless your servers are on ships or airplanes. You'll need to write a little custom code around this but it shouldn't be too hard. This is the solution I like the best.

Last, you can do nothing and wait for SQL Server 2000. It includes a handy little function called GetUTCDate() which returns GMT time. You can use it just like you would GetDate(). For example,

SET @utcdate = GetUTCDate()

You can compare the results of GetDate() and GetUTCDate() and determine the time zone. Or you could just store everything in GMT time and then let the clients adjust it on the fly.

Related Articles

Application Locks (or Mutexes) in SQL Server 2005 (7 January 2008)

What I Wish Developers Knew About SQL Server (Presentation) (11 October 2007)

Multiple Active Result Sets (MARS) – Transactions and Debugging (17 June 2007)

Multiple Active Result Sets (MARS) (3 April 2007)

How SQL Server 2005 Enables Service-Oriented Database Architectures (8 September 2006)

Presentation: What I Wish Developers Knew About SQL Server (17 November 2005)

GeoCoding with SQL Server and Google (8 August 2005)

How to Asynchronously Execute a DTS package from ASP or ASP.NET (27 March 2005)

Other Recent Forum Posts

Need Help in SQL query optimization (2d)

Info needed on field "user_scans" of table "sys.dm_db_index_usage_stats" (3d)

Suspect Parameter Sniffing? (3d)

See values of a proc when it is called from another proc? (4d)

Varchar() datatype and C# SqlDataReader truncates string (4d)

Find the extra rows (4d)

How to represent birth and death record in mysql db without duplicating data of an existing persons table existing persons table (5d)

EPR updates issues (5d)

- Advertisement -