8

Below is what I pieced together but I wanted to see what other ways are available.

SET NOCOUNT ON;
GO
DECLARE @tz VARCHAR(50)
EXEC [master].[dbo].[xp_regread]
    'HKEY_LOCAL_MACHINE'
    ,'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
    ,'TimeZoneKeyName'
    ,@tz OUT;

SELECT 
    GETDATE()
    ,'(' + LEFT(PARSENAME(REPLACE(@tz, ' ','.'),3),1) 
    + '' + LEFT(PARSENAME(REPLACE(@tz, ' ','.'),2),1) 
    + '' + LEFT(PARSENAME(REPLACE(@tz, ' ','.'),1),1) +')'

Output: 2014-10-14 16:22:21.037 (CST)

1
  • Your goal is to get the server's timezone?
    – Erik
    Commented Aug 13, 2015 at 22:35

1 Answer 1

4

Reading the reg key with this or SQLCLR is the only correct way I know (I personally would create a job which updates a table instead of enabling xp_regread though.).

This powershell script is an example of how to update a config table with this information.

$timeZone = (get-itemproperty 'HKLM:\SYSTEM\CurrentControlSet\Control\TimeZoneInformation\').TimeZoneKeyName

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server=.;database=myDatabase;trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = "
        MERGE ConfigTable AS target
        USING (SELECT 'TimeZone', @timeZone) AS source (ConfigKey, ConfigValue)
        ON (target.ConfigKey = source.ConfigKey)
        WHEN MATCHED THEN UPDATE SET ConfigValue = source.ConfigValue
        WHEN NOT MATCHED THEN INSERT (ConfigKey, ConfigValue) VALUES (source.ConfigKey, source.ConfigValue)" 
$command.Parameters.AddWithValue("@timeZone", $timeZone)
$Command.ExecuteNonQuery()
$Connection.Close()

All other systems with offset etc are giving incorrect results.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.