What is the best way to figure out what time zone the server is running in?

Question

Post reply

What is the best way to figure out what time zone the server is running in?

me_balaji

SSC Eights!

Points: 923
More actions
March 13, 2006 at 2:01 pm

#189720

What is the best way to figure out what time zone the server is running in SQLSERVER.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Run SQLdiag.exe and look in the [System Summary] section.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

me_balaji SSC Eights! Points: 923 More actions · Answer 2

I think I didn't mention exactly what I am looking for. This is what I need to do.

I need to capture the time zone of the DB server and adjust some date columns in some of my tables to make sure all the dates are using PST.

My database is distributed into different smaller databases (example into laptops, PDAs etc..) around the world.

End of day I will sync all the databases in PST time zone. Is there a command in SQL to capture the timezone.

Thanks

Perry-300990 Default port Points: 1451 More actions · Answer 3

Heh, maybe you could sp_help_targetserver if you made a link to the local server

TDuffy SSCarpal Tunnel Points: 4170 More actions · Answer 4

Comparing the computer time to GMT using GETUTCDATE() may proivde an answer.

Terry

R2ro Default port Points: 1439 More actions · Answer 5

You might want try xp_regread depending upon your needs. Checking values in:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation

The Bias and ActiveTimeBias will give you offsets from UTC in minutes for Standard Time and the current time. These values will be different during DST if it is observed in the TZ in question.

Did I say DST? <ack!> Now, there's a mess!! There is also an entry, DisableAutoDaylightTimeSet, in the same key if the "Automatically adjust clock for daylight saving changes" checkbox on the Time Zone tab of the Date and Time Properties dialog is checked. If it is not checked there is no entry!

There are some issues... Whether regread'ing or GETUTCDATE()'ing all systems involved need to have the OS updates applied regularly. The start/end of DST will change next year. Additionally, these 'simple' mechanisms are only valid for the current DST cycle as they are based on single value combinations. DST's observation, start/end dates/times and the time shifts themselves have (and will) change.

The GETDATE()/GETUTCDATE() method is approx 50x faster than the regread but you will end up with gaps/overlaps in your times when changing from/to DST and translating to PST.

I am in the process of developing a mechanism for SQL Server of determining the corresponding UTC for any datetime from any TZ. Talk about a headache!!

If your situation allows I would recommend adjusting the times to UTC as the base TZ for your consolidated db and report times as needed in other TZ's using either of the options above to determine the time offset.

HTH,

Art