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