Have a look at

select Sysdatetimeoffset()

This gives the timezone offset of the server in relation to UTC.

Small side note, perhaps make it a thing to store all dates as UTC, this way you in SQL and well as the developers can deal with the date and convert it to local dates easily as there is no guessing was the data entered by a server on a server standing in Asia or in the US. The default for server time is GetDate(), the same for UTC is GetUtcDate(), not too hard to do...

You can read the time zone from the registry (you will need permission that opens lots of security issues and is usually not given) like this:

DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'TimeZoneKeyName',@TimeZone OUT
SELECT @TimeZone

Update based on your answer

As mentioned above., you can use Sysdatetimeoffset(), Unless you know that LAT/ LONG or are able to use .Net and created extended property the only TSQL way to get approximately value by getting the offset and map that with the time zone names in SQL server.

Here are 2 samples, the first takes the timezone offset and places it in a variable, then query the table with the offset variable

declare @offset sysname
SELECT top(1) @offset = value 
from String_Split(cast(Sysdatetimeoffset() as varchar(50)),' ')
order by value;

SELECT [name] FROM sys.time_zone_info where [current_utc_offset]= @offset;

or use a subquery, basically two ways to do the same same thing

SELECT [name] FROM sys.time_zone_info 
where [current_utc_offset] in ( SELECT top(1) value 
from String_Split(cast(Sysdatetimeoffset() as varchar(50)),' ')
order by value)

If you do not want to read the registry and you need to be precise then use CLR user-defined function and deploy that to your database

You could do this with relatively few lines of code:

using System;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = Microsoft.SqlServer.Server.DataAccessKind.None
        ,IsDeterministic =true
        ,SystemDataAccess = Microsoft.SqlServer.Server.SystemDataAccessKind.None
        ,Name = "TimeZoneName"
        
        )]
    public static SqlString TimeZoneName()
    {
        return new SqlString(TimeZone.CurrentTimeZone.StandardName);
    }
}

A good sample for creating, deploying etc is here https://sqlclr-tutorial.net/en/tutorial/100006/clr-user-defined-functions

As you access no objects in SQL and perform or external access all SQL instances could allow this as it's safe code

Based on info here: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/timezones-overview

CREATE FUNCTION udf_CurrentTimeZoneId()
RETURNS nvarchar(max)
AS
BEGIN

DECLARE @result nvarchar(max);
DECLARE @tz nvarchar(max);
select @tz = CURRENT_TIMEZONE();

select @result =
CASE
when @tz = '(UTC-12:00) International Date Line West' then 'Dateline Standard Time'
when @tz = '(UTC-11:00) Coordinated Universal Time-11' then 'UTC-11'
when @tz = '(UTC-10:00) Aleutian Islands' then 'Aleutian Standard Time'
when @tz = '(UTC-10:00) Hawaii' then 'Hawaiian Standard Time'
when @tz = '(UTC-09:30) Marquesas Islands' then 'Marquesas Standard Time'
when @tz = '(UTC-09:00) Alaska' then 'Alaskan Standard Time'
when @tz = '(UTC-09:00) Coordinated Universal Time-09' then 'UTC-09'
when @tz = '(UTC-08:00) Baja California' then 'Pacific Standard Time (Mexico)'
when @tz = '(UTC-08:00) Coordinated Universal Time-08' then 'UTC-08'
when @tz = '(UTC-08:00) Pacific Time (US & Canada)' then 'Pacific Standard Time'
when @tz = '(UTC-07:00) Arizona' then 'US Mountain Standard Time'
when @tz = '(UTC-07:00) Chihuahua, La Paz, Mazatlan' then 'Mountain Standard Time (Mexico)'
when @tz = '(UTC-07:00) Mountain Time (US & Canada)' then 'Mountain Standard Time'
when @tz = '(UTC-06:00) Central America' then 'Central America Standard Time'
when @tz = '(UTC-06:00) Central Time (US & Canada)' then 'Central Standard Time'
when @tz = '(UTC-06:00) Easter Island' then 'Easter Island Standard Time'
when @tz = '(UTC-06:00) Guadalajara, Mexico City, Monterrey' then 'Central Standard Time (Mexico)'
when @tz = '(UTC-06:00) Saskatchewan' then 'Canada Central Standard Time'
when @tz = '(UTC-05:00) Bogota, Lima, Quito, Rio Branco' then 'SA Pacific Standard Time'
when @tz = '(UTC-05:00) Chetumal' then 'Eastern Standard Time (Mexico)'
when @tz = '(UTC-05:00) Eastern Time (US & Canada)' then 'Eastern Standard Time'
when @tz = '(UTC-05:00) Haiti' then 'Haiti Standard Time'
when @tz = '(UTC-05:00) Havana' then 'Cuba Standard Time'
when @tz = '(UTC-05:00) Indiana (East)' then 'US Eastern Standard Time'
when @tz = '(UTC-05:00) Turks and Caicos' then 'Turks And Caicos Standard Time'
when @tz = '(UTC-04:00) Asuncion' then 'Paraguay Standard Time'
when @tz = '(UTC-04:00) Atlantic Time (Canada)' then 'Atlantic Standard Time'
when @tz = '(UTC-04:00) Caracas' then 'Venezuela Standard Time'
when @tz = '(UTC-04:00) Cuiaba' then 'Central Brazilian Standard Time'
when @tz = '(UTC-04:00) Georgetown, La Paz, Manaus, San Juan' then 'SA Western Standard Time'
when @tz = '(UTC-04:00) Santiago' then 'Pacific SA Standard Time'
when @tz = '(UTC-03:30) Newfoundland' then 'Newfoundland Standard Time'
when @tz = '(UTC-03:00) Araguaina' then 'Tocantins Standard Time'
when @tz = '(UTC-03:00) Brasilia' then 'E. South America Standard Time'
when @tz = '(UTC-03:00) Cayenne, Fortaleza' then 'SA Eastern Standard Time'
when @tz = '(UTC-03:00) City of Buenos Aires' then 'Argentina Standard Time'
when @tz = '(UTC-03:00) Greenland' then 'Greenland Standard Time'
when @tz = '(UTC-03:00) Montevideo' then 'Montevideo Standard Time'
when @tz = '(UTC-03:00) Punta Arenas' then 'Magallanes Standard Time'
when @tz = '(UTC-03:00) Saint Pierre and Miquelon' then 'Saint Pierre Standard Time'
when @tz = '(UTC-03:00) Salvador' then 'Bahia Standard Time'
when @tz = '(UTC-02:00) Coordinated Universal Time-02' then 'UTC-02'
when @tz = '(UTC-02:00) Mid-Atlantic - Old' then 'Mid-Atlantic Standard Time'
when @tz = '(UTC-01:00) Azores' then 'Azores Standard Time'
when @tz = '(UTC-01:00) Cabo Verde Is.' then 'Cabo Verde Standard Time'
when @tz = '(UTC) Coordinated Universal Time' then 'UTC'
when @tz = '(UTC+00:00) Dublin, Edinburgh, Lisbon, London' then 'GMT Standard Time'
when @tz = '(UTC+00:00) Monrovia, Reykjavik' then 'Greenwich Standard Time'
when @tz = '(UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna' then 'W. Europe Standard Time'
when @tz = '(UTC+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague' then 'Central Europe Standard Time'
when @tz = '(UTC+01:00) Brussels, Copenhagen, Madrid, Paris' then 'Romance Standard Time'
when @tz = '(UTC+01:00) Casablanca' then 'Morocco Standard Time'
when @tz = '(UTC+01:00) Sao Tome' then 'Sao Tome Standard Time'
when @tz = '(UTC+01:00) Sarajevo, Skopje, Warsaw, Zagreb' then 'Central European Standard Time'
when @tz = '(UTC+01:00) West Central Africa' then 'W. Central Africa Standard Time'
when @tz = '(UTC+02:00) Amman' then 'Jordan Standard Time'
when @tz = '(UTC+02:00) Athens, Bucharest' then 'GTB Standard Time'
when @tz = '(UTC+02:00) Beirut' then 'Middle East Standard Time'
when @tz = '(UTC+02:00) Cairo' then 'Egypt Standard Time'
when @tz = '(UTC+02:00) Chisinau' then 'E. Europe Standard Time'
when @tz = '(UTC+02:00) Damascus' then 'Syria Standard Time'
when @tz = '(UTC+02:00) Gaza, Hebron' then 'West Bank Standard Time'
when @tz = '(UTC+02:00) Harare, Pretoria' then 'South Africa Standard Time'
when @tz = '(UTC+02:00) Helsinki, Kyiv, Riga, Sofia, Tallinn, Vilnius' then 'FLE Standard Time'
when @tz = '(UTC+02:00) Jerusalem' then 'Israel Standard Time'
when @tz = '(UTC+02:00) Kaliningrad' then 'Kaliningrad Standard Time'
when @tz = '(UTC+02:00) Khartoum' then 'Sudan Standard Time'
when @tz = '(UTC+02:00) Tripoli' then 'Libya Standard Time'
when @tz = '(UTC+02:00) Windhoek' then 'Namibia Standard Time'
when @tz = '(UTC+03:00) Baghdad' then 'Arabic Standard Time'
when @tz = '(UTC+03:00) Istanbul' then 'Türkiye Standard Time'
when @tz = '(UTC+03:00) Kuwait, Riyadh' then 'Arab Standard Time'
when @tz = '(UTC+03:00) Minsk' then 'Belarus Standard Time'
when @tz = '(UTC+03:00) Moscow, St. Petersburg' then 'Russian Standard Time'
when @tz = '(UTC+03:00) Nairobi' then 'E. Africa Standard Time'
when @tz = '(UTC+03:30) Tehran' then 'Iran Standard Time'
when @tz = '(UTC+04:00) Abu Dhabi, Muscat' then 'Arabian Standard Time'
when @tz = '(UTC+04:00) Astrakhan, Ulyanovsk' then 'Astrakhan Standard Time'
when @tz = '(UTC+04:00) Baku' then 'Azerbaijan Standard Time'
when @tz = '(UTC+04:00) Izhevsk, Samara' then 'Russia Time Zone 3'
when @tz = '(UTC+04:00) Port Louis' then 'Mauritius Standard Time'
when @tz = '(UTC+04:00) Saratov' then 'Saratov Standard Time'
when @tz = '(UTC+04:00) Tbilisi' then 'Georgian Standard Time'
when @tz = '(UTC+04:00) Volgograd' then 'Volgograd Standard Time'
when @tz = '(UTC+04:00) Yerevan' then 'Caucasus Standard Time'
when @tz = '(UTC+04:30) Kabul' then 'Afghanistan Standard Time'
when @tz = '(UTC+05:00) Ashgabat, Tashkent' then 'West Asia Standard Time'
when @tz = '(UTC+05:00) Ekaterinburg' then 'Ekaterinburg Standard Time'
when @tz = '(UTC+05:00) Islamabad, Karachi' then 'Pakistan Standard Time'
when @tz = '(UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi' then 'India Standard Time'
when @tz = '(UTC+05:30) Sri Jayawardenepura' then 'Sri Lanka Standard Time'
when @tz = '(UTC+05:45) Kathmandu' then 'Nepal Standard Time'
when @tz = '(UTC+06:00) Nur-Sultan' then 'Central Asia Standard Time'
when @tz = '(UTC+06:00) Dhaka' then 'Bangladesh Standard Time'
when @tz = '(UTC+06:00) Omsk' then 'Omsk Standard Time'
when @tz = '(UTC+06:30) Yangon (Rangoon)' then 'Myanmar Standard Time'
when @tz = '(UTC+07:00) Bangkok, Hanoi, Jakarta' then 'SE Asia Standard Time'
when @tz = '(UTC+07:00) Barnaul, Gorno-Altaysk' then 'Altai Standard Time'
when @tz = '(UTC+07:00) Hovd' then 'W. Mongolia Standard Time'
when @tz = '(UTC+07:00) Krasnoyarsk' then 'North Asia Standard Time'
when @tz = '(UTC+07:00) Novosibirsk' then 'N. Central Asia Standard Time'
when @tz = '(UTC+07:00) Tomsk' then 'Tomsk Standard Time'
when @tz = '(UTC+08:00) Beijing, Chongqing, Hong Kong, Urumqi' then 'China Standard Time'
when @tz = '(UTC+08:00) Irkutsk' then 'North Asia East Standard Time'
when @tz = '(UTC+08:00) Kuala Lumpur, Singapore' then 'Singapore Standard Time'
when @tz = '(UTC+08:00) Perth' then 'W. Australia Standard Time'
when @tz = '(UTC+08:00) Taipei' then 'Taipei Standard Time'
when @tz = '(UTC+08:00) Ulaanbaatar' then 'Ulaanbaatar Standard Time'
when @tz = '(UTC+08:45) Eucla' then 'Aus Central W. Standard Time'
when @tz = '(UTC+09:00) Chita' then 'Transbaikal Standard Time'
when @tz = '(UTC+09:00) Osaka, Sapporo, Tokyo' then 'Tokyo Standard Time'
when @tz = '(UTC+09:00) Pyongyang' then 'North Korea Standard Time'
when @tz = '(UTC+09:00) Seoul' then 'Korea Standard Time'
when @tz = '(UTC+09:00) Yakutsk' then 'Yakutsk Standard Time'
when @tz = '(UTC+09:30) Adelaide' then 'Cen. Australia Standard Time'
when @tz = '(UTC+09:30) Darwin' then 'AUS Central Standard Time'
when @tz = '(UTC+10:00) Brisbane' then 'E. Australia Standard Time'
when @tz = '(UTC+10:00) Canberra, Melbourne, Sydney' then 'AUS Eastern Standard Time'
when @tz = '(UTC+10:00) Guam, Port Moresby' then 'West Pacific Standard Time'
when @tz = '(UTC+10:00) Hobart' then 'Tasmania Standard Time'
when @tz = '(UTC+10:00) Vladivostok' then 'Vladivostok Standard Time'
when @tz = '(UTC+10:30) Lord Howe Island' then 'Lord Howe Standard Time'
when @tz = '(UTC+11:00) Bougainville Island' then 'Bougainville Standard Time'
when @tz = '(UTC+11:00) Chokurdakh' then 'Russia Time Zone 10'
when @tz = '(UTC+11:00) Magadan' then 'Magadan Standard Time'
when @tz = '(UTC+11:00) Norfolk Island' then 'Norfolk Standard Time'
when @tz = '(UTC+11:00) Sakhalin' then 'Sakhalin Standard Time'
when @tz = '(UTC+11:00) Solomon Is., New Caledonia' then 'Central Pacific Standard Time'
when @tz = '(UTC+12:00) Anadyr, Petropavlovsk-Kamchatsky' then 'Russia Time Zone 11'
when @tz = '(UTC+12:00) Auckland, Wellington' then 'New Zealand Standard Time'
when @tz = '(UTC+12:00) Coordinated Universal Time+12' then 'UTC+12'
when @tz = '(UTC+12:00) Fiji' then 'Fiji Standard Time'
when @tz = '(UTC+12:00) Petropavlovsk-Kamchatsky - Old' then 'Kamchatka Standard Time'
when @tz = '(UTC+12:45) Chatham Islands' then 'Chatham Islands Standard Time'
when @tz = '(UTC+13:00) Coordinated Universal Time+13' then 'UTC+13'
when @tz = '(UTC+13:00) Nuku''alofa' then 'Tonga Standard Time'
when @tz = '(UTC+13:00) Samoa' then 'Samoa Standard Time'
when @tz = '(UTC+14:00) Kiritimati Island' then 'Line Islands Standard Time'
END;

RETURN @result;

END

Then get it like:

select dbo.udf_CurrentTimeZoneId()

Can now create things like:

CREATE FUNCTION udf_UtcDateTime(@dt Datetime) 
RETURNS DATETIME
AS 
BEGIN
DECLARE @result datetime = null;
SELECT @result = @dt AT TIME ZONE 'UTC' AT TIME ZONE dbo.udf_CurrentTimeZoneId();
RETURN @result;
END

To get local datetime in utc:

select dbo.udf_utcdatetime('1987-01-01T12:00:00')