CODEWITHSUNDEEP
asp.netsqltimezoneutc
Bakchod Guru
Bakchod Guru

Reputation: 51

SQL Server dates and servers in 3 different time zones

I thought this was the correct way to do this, but apparently it is not.

I write a date to an SQL Server database using the default of GetUtcDate().

I read it back in ASP.NET and display it to the user with theTime.ToLocalTime(). (It's already in UTC, so I need to convert it to localtime, NOT into UTC time again.)

Wouldn't that make the date/time display correctly any where in the world?

But it seems to be 1 hour off when:

The SQL Server is in 1 time zone (might be... or not be in a DST area or time of year).

The webserver running the ASP.NET is in a different timezone (it also may or may not be DST there).

The local user viewing the date/time is also in a totally different timezone (also with or without DST there).

(I'm not sure if it matters, but the webserver only has .net v1.1 and will NEVER be upgraded to v2 or anything else.)

Upvotes: 0

Views: 606

Answers (1)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241633

Storing UTC values using GETUTCDATE is fine. Retrieving UTC values into a DateTime object on your web server is fine also. Calling ToLocalTime is not.

When you use ToLocalTime, you are using the local time zone of the machine where the code is running. In this case - your web server.

The web server has absolutely no knowledge of the timezone of your user. You have to manage that yourself in your application logic. Since you are stuck on .Net 1.1, you have no easy way to manage this server side. You'll have to take a client-side JavaScript approach:

  • Pass the UTC value to the client in a non-displaying manner. For example, an XHR call, or inline script.
  • Preferably, use the ISO-8601 format, such as 2014-06-10T09:30:00.000Z. You can get this from your DateTime object by using .ToString("o").
  • On the client, if you're using newer web browsers, you can pass this string directly into the constructor of a JavaScript Date object. However, you may prefer to use a more robust library for browser compatibility and enhanced formatting capabilities. I recommend moment.js.
  • By passing the UTC value to the browser, you've delegated the work of figuring out the user's time zone. Calling any of the output methods on the Date or moment objects will show the time converted to the user's time zone automatically.

There are plenty of server-side approaches, but they would require you update to a more reasonable version of the .NET Framework.

With any of the server-side mechanisms, you would need to change your application to:

  • Ask the user for their time zone in some settings part of your application.
  • Apply that time zone to the UTC date before delivering a string to the web browser.

Also, you should recognize that .NET 1.1 was released in 2003, and is way past its lifecycle end date. You should review the chart here and consider upgrading.

Upvotes: 1

Related Questions