Is it possible to get timezone city information in SQL ? In below format to be precise

I want to get the timezone of a machine in the following format using a stored procedure but all I have managed is the UTC+5.30 part yet.

My progress so far:

CREATE PROCEDURE [dbo].[UspGetTimeZone]

AS
SET NOCOUNT ON;


BEGIN

DECLARE @TimeZone VARCHAR(50),
@DetailedTimeZone VARCHAR(50)

set @DetailedTimeZone = (SELECT
SYSDATETIMEOFFSET() AS SYSDATETIMEOFFSET )



DECLARE @CISDB TABLE
(
TimeZone NVARCHAR(100)
)

Insert Into @CISDB( TimeZone)
VALUES ( 'UTC' + (SELECT SUBSTRING(@DetailedTimeZone , 29, 9) AS OFFSET))
select * from @CISDB

END


1 Answer
1

This info is saved into registry,and you can read these data via using xp_regread

xp_regread

so I hope next query helps you:-

CREATE PROCEDURE [dbo].[UspGetTimeZone]
AS
SET NOCOUNT ON;

BEGIN

DECLARE @TimeStandared VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEMCurrentControlSetControlTimeZoneInformation',
'TimeZoneKeyName',@TimeStandared OUT

DECLARE @TimeZone VARCHAR(50),
@keyname varchar(200)
set @keyname = 'SOFTWAREMicrosoftWindows NTCurrentVersionTime Zones' + @TimeStandared
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@keyname, 'Display',@TimeZone OUT
SELECT @TimeZone

END


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.