javascript - Convert UTC timezone to local time zone - Stack Overflow

admin管理员组

文章数量:1431434

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

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

• javascript
• sql
• sql-server
• timezone

Share Improve this question Follow asked Nov 19, 2024 at 10:28 Chris HadfieldChris Hadfield 52422 gold badges1010 silver badges3434 bronze badges 4

• You can run: select * from sys.time_zone_info and see the names of zones you need to pass – siggemannen Commented Nov 19, 2024 at 10:41
• If you can't pass the correct timezone, why not fetch data from sql server in utc and then do the conversion in the javascript scripts of things before generating the excel-file. – siggemannen Commented Nov 19, 2024 at 10:51
• @siggemannen I can conver to local time in javascript but how can i do in excel's data. Excel is exported from backend. – Chris Hadfield Commented Nov 19, 2024 at 14:07
• How, sql server can't create files? And what's your backend running – siggemannen Commented Nov 19, 2024 at 14:12

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

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.

本文标签： javascriptConvert UTC timezone to local time zoneStack Overflow