Getting the time zone

By Bill Graziano on 20 July 2000 | 1 Comment | 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,

DECLARE @utcdate DATETIME
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.

Discuss this article: 1 Comment so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

Count in Case Statement (4 Replies)

Subquery (2 Replies)

Error: Could not create managed connection manager (2 Replies)

PIVOT multiple columns (4 Replies)

Dynamic Statement Needed??? (5 Replies)

how to reduse the size of .ldf file (1 Reply)

NOT IN statement? (19 Replies)

SQL Query from packet capture (6 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -