Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – 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

Tags:

2

Answers


  1. 0

    If you’re using JavaScript to pass timezone name, why not use JavaScript to display the date. All you have to do is provide the date in ISO8601 format:

    let date = new Date("2024-11-19T10:34:14.682Z");
console.log(date, date.toLocaleString());
    Login or Signup to reply.
  2. 0

    To ensure compatibility with SQL Server, you should use IANA timezone IDs (like "Asia/Kathmandu"), which are recognized by both JavaScript and SQL Server. In JavaScript, you can get the user’s timezone like this:

    const timeZoneName = Intl.DateTimeFormat().resolvedOptions().timeZone;
console.log(timeZoneName); // Outputs something like "Asia/Kathmandu"

    This gives you a reliable and valid timezone ID.

    Once you have a valid timezone ID, pass it to your SQL procedure to handle the conversion. Here’s how the query should look:

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

    Make sure @TimeZoneName contains a valid timezone ID like the one from JavaScript.

    If you’re displaying dates directly in the browser, you can skip the server-side conversion and handle it all in JavaScript. Just send the UTC date in ISO 8601 format (e.g., "2024-11-19T10:34:14.682Z") and let JavaScript handle the conversion:

    const date = new Date("2024-11-19T10:34:14.682Z");
console.log(date.toLocaleString()); // Converts to the user's local timezone

    This approach is simpler if you don’t need to worry about timezone consistency across different users.

    Let me know if you need further clarification

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search