4

I am working on an application whose targeted audience is located throughout the globe. My development platform is ASP.Net/Sql Server both are currently hosted on same dediacated server at present.

There could be possibility that Sql Server may be moved into different server and web application to be hosted on different server. Time zone may be different for both the servers.

Currently we are into development and need to handle Universal Date time concept rather than fighting at later stages.

Our database currently is structured with following type of columns for datetime.

DateTime(mm/dd/yyyy hh:MM:ss), SmallDateTime(holds date part only mm/dd/yyyy 12:00:00), Varchar(holds hhMMss in 24 hour format only)

Currently we are saving GetDate() of Sql Server, DateTime.Now of C# i.e. ASP.NET, javascript datetime and any other date directly. No UTC conversion or nothing like that is applicable at the moment. We are neither storing any TimeZone or any offset at the moment.

How do I handle DateTime related issues when

  • Calling Stored procedure to Save DateTime from UI to Database

  • Display data from Stored procedure to UI

  • Display data from ASP.NET Server to Client Browser

  • Save data from Client Browser to ASP.NET Server

Please suggest some code examples for each of the cases along with Daylight Saving

Shantanu Gupta
  • 20,688
  • 54
  • 182
  • 286
  • Related question: http://stackoverflow.com/questions/2532729/daylight-saving-time-and-timezone-best-practices – Oded Nov 28 '11 at 10:09

1 Answers1

0

I work in the company that has business all over the world and we store all dates in UTC.

Basically, in your sql you have to use GETUTCDATE() function instead of GETDATE() and in C# you use DateTime.UtcNow instead of DateTime.Now.

Database fields could be standard datetime

In your UI you can display date in yyyy/MM/dd format if you don't know or cannot figure out client's local settings.

Eugene Z
  • 151
  • 1
  • 6
  • How are you handling daylight saving. – Shantanu Gupta Dec 13 '11 at 16:01
  • Difficult. Some countries observe it, some don't. Whithin the same conutry, some municiplities observe it and some not. In many countries it is local decision. In USA some states don't observe it. The date of switch is also varies. You cannot base your decision on country name or coordinates. – Eugene Z Dec 13 '11 at 17:47