Convert UTC timezone to local time zone

I have a report where all the dates should be displayed in the client's time zone. The report includes a feature that allows exporting to Excel. For both generating the report and exporting it to Excel, I am using the same procedure. All the dates are stored in UTC in the database. I tried to convert the UTC date to the client's local time zone using the following code in SQL Server:

DateUtc AT TIME ZONE 'UTC' AT TIME ZONE @TimeZoneName AS dateTimeCreated

@TimeZoneName is passed via procedure parameter. I tried to get the name of client's time zone name in javascript by following code.

const date = new Date();

const timeZoneString = new Intl.DateTimeFormat('en-US', {
    timeZoneName: 'long'
}).format(date);

const timeZoneName = timeZoneString.split(',')[1].trim();

console.log(timeZoneName)

This code doesn't return consistent values. For example, if I am in the (UTC-12:00) International Date Line West time zone, JavaScript returns the value as GMT-12:00. However, if I am in Nepal Time, it returns a random value for each time zone. If I pass the exact value provided by JavaScript to the procedure, the procedure throws an exception as shown below:

The time zone parameter 'Nepal Time' provided to AT TIME ZONE clause is invalid.

How to resolve this issue? Anyone knows the answer please help me I tried to use the TimeZoneOffset value, but it causes issues during daylight saving time

Solution

JavaScript Intl API can get you the IANA time zone name:

let timezoneName_iana = Intl.DateTimeFormat().resolvedOptions().timeZone;
// Asia/Kathmandu

If you're using .NET 6+ as the bridge between the browser and SQL Server you may use the TimeZoneInfo.TryConvertIanaIdToWindowsId method:

string timezoneName_iana = "Asia/Kathmandu";
TimeZoneInfo.TryConvertIanaIdToWindowsId(timezoneName_iana, out string? timezoneName_win);
// Nepal Standard Time

This name should work with AT TIME ZONE function in SQL Server.