patternsqlMinor
Best way to get time zone key name from SQL Server
Viewed 0 times
zonesqlwaytimegetnameserverfromkeybest
Problem
Below is what I pieced together but I wanted to see what other ways are available.
Output: 2014-10-14 16:22:21.037 (CST)
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)
Solution
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.
All other systems with offset etc are giving incorrect results.
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.
Context
StackExchange Database Administrators Q#80196, answer score: 4
Revisions (0)
No revisions yet.