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,

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.