Post reply

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

  • 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.

  • March 13, 2006 at 3:12 pm

    #626384

    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

  • March 13, 2006 at 4:08 pm

    #626392

    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

     

  • March 13, 2006 at 9:53 pm

    #626414

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

  • March 14, 2006 at 8:23 am

    #626537

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

     

    Terry

  • March 16, 2006 at 1:47 pm

    #627158

    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

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

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