0

I just can't seem to find an answer for this hence the post. I want the time stored in the SQL database to be displayed on my website to be the exact time that is in the database. Currently, this is not the case. The time being displayed is being converted to the browsing computers local time. Since the time being saved is an appointment time, regardless of where or what time zone the browsing computer is in, having it converted makes no sense. I thought that the methods TimeZone and TimeZoneInfo were used to convert the time to local time, but this seems to be happening automatically. How do I get the exact time saved to be the time displayed on my site?

The SQL database fields ETAStart and ETAEnd are defined as datetime.

Updates to the database are handled in the controller as such:

if (TryUpdateModel(model)) {

    System.DateTime ETAEnd = Convert.ToDateTime(model.ETAStart).AddHours(3);

    model.ETAEnd = ETAEnd;
    model.UpdatedBy = CurrentUser();

    model.UpdateDate = Convert.ToDateTime(DateTime.Now.ToString("U"));


    dc.SubmitChanges();


    return View(new GridModel<ManifestMasterModel> { Data = GetMMList(0) });

} else {
    return View();
}

Thank you in advance and I am very appreciative to all who respond.

natem345
  • 93
  • 5
AZee
  • 205
  • 4
  • 9
  • Thank you all. I have tracked the problem to a jQuery file that is converting the time. I verified that the data being returned from the db is identical regardless of what timezone the browsing computer. I will be pursuing this angle and if I still have issues I will post as a new question more in line with jQuery. Thanks again! – AZee Apr 25 '12 at 00:51

3 Answers3

0

Did you check what Time Zone is set on server? It happens to me few days back and it turned out hosting server have different timezone.

Here is way to check:

Response.Write(TimeZoneInfo.Local.DisplayName)

Edit 1:

You can use Utc time to convert it into your desire timezone. See:

TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time"));
Charandeep Singh
  • 942
  • 2
  • 7
  • 17
  • The timezone on the web server and the SQL server are what I want them to be and are identical. Accessing the site from other servers that have different timezones discloses the problem I outlined. – AZee Apr 21 '12 at 20:09
  • There is no way to figure out client timezone. So issue is something else. See Edit 1. – Charandeep Singh Apr 21 '12 at 20:21
  • Charandeep, thank you for your replies. I have tried using TimeZone and TimeZoneInfo and setting the time to "Mountain Standard Time", however; some parts of this time zone use daylightsavings and others don't. There is no consistency. I was hoping there would be a way to display the time in the db and not converted at all. What I would ultimately have to do is determine what the time is being converted to and then calculate a way to convert it back to what is being stored in the db. I know there are other methods such as IsDaylightSaving and GMTOffset but why? I just need the db value. – AZee Apr 21 '12 at 21:53
  • Why do you want to convert it back, just store once what you will need and use it? – Charandeep Singh Apr 22 '12 at 05:08
  • That is the entire problem... the time being saved to the db is correct, the time being retrieved and displayed is being converted to the browsing computers time zone - automatically. – AZee Apr 23 '12 at 18:15
0

Are you trying to store datetime as UTC with this:

model.UpdateDate = Convert.ToDateTime(DateTime.Now.ToString("U"));

You can use it like this for not doing conversions:

model.UpdateDate = DateTime.Now.ToUniversalTime();
Nesim Razon
  • 9,684
  • 3
  • 36
  • 48
  • Nesim, This is just a field in the db to determine when it was last updated. Using the "U" format returns the date as "Monday, June 16, 2012 4:15:07 AM". This is not the fields that I am having an issue with. – AZee Apr 21 '12 at 21:47
  • Using "U" format returns date as you mention, but the part I didn't understand is your are converting it to DateTime again which you are losing format. Both lines will gave you same result that I wrote in my answer. – Nesim Razon Apr 21 '12 at 21:56
  • And with your actual problem, can you show how are you getting date from database and trying to display? – Nesim Razon Apr 21 '12 at 21:56
  • //ETAStart = TimeZoneInfo.ConvertTimeToUtc(Convert.ToDateTime(e.ETAStart), tz), //ETAEnd = TimeZoneInfo.ConvertTimeToUtc(Convert.ToDateTime(e.ETAEnd), tz), //ETAStart = zone.ToLocalTime(Convert.ToDateTime(e.ETAStart)), //ETAEnd = zone.ToLocalTime(Convert.ToDateTime(e.ETAEnd)), //ETAStart = DateTime.SpecifyKind(Convert.ToDateTime(e.ETAStart), DateTimeKind.Unspecified), //ETAEnd = DateTime.SpecifyKind(Convert.ToDateTime(e.ETAEnd), DateTimeKind.Unspecified), ETAStart = Convert.ToDateTime(e.ETAStart), ETAEnd = Convert.ToDateTime(e.ETAEnd), failed attempts commented – AZee Apr 21 '12 at 22:04
  • Please don't get me wrong, It is hard to find solutions without getting more input, so I am asking; Do you know when you use "U" formating you are storing your local time as UTC time which means, If you are on GMT+2 and your local time is 14:00, your date will be saved as 12:00. – Nesim Razon Apr 21 '12 at 22:10
  • And can't see a date assigning to model.ETAStart? Where and how are dates assigning to it. And is model.ETAStart type is string? Because there is datetime conversion there. I am suspicious about these conversions. – Nesim Razon Apr 21 '12 at 22:32
  • [UIHint("Time"), Required] public DateTime ETAStart { get; set; } [UIHint("Time"), Required] public DateTime ETAEnd { get; set; } The conversion is being done because of "cannot implicitly convert System.DateTime? to System.DatTime Please forget the "U" formatting, this is not the issue or even one of the fields in question. – AZee Apr 23 '12 at 03:31
0

I'm not completely sure I understand what the issue is, but it sounds to me like you need to implement a custom modelbinder.

These resources might help you:

Timezone Strategy

Custom DateTime model binder in Asp.net MVC

http://weblogs.asp.net/melvynharbour/archive/2008/11/21/mvc-modelbinder-and-localization.aspx

Although, I don't think there's anything wrong with the quick-fix suggestion of just using a string (I think you would be doing yourself a favor). It sounds like you're trying to get the client and MVC to ignore the Time Zone implications of dealing with dates, which will probably take a lot more work and be a lot less clean than just using the string approach. You could still have a DateTime in the database, just pull the string value the user submitted and parse that into a datetime using the appropriate time zone.

Community
  • 1
  • 1
Shaun Rowan
  • 9,269
  • 4
  • 28
  • 52
  • Shaun, the issue is that the appointment time should not be converted. If the appointment time is created in California for 9am for a customer in Utah and the website is viewed in Utah it should show 9am but is being converted to 8am. All references I have seen on this subject refer to displaying the date and time in the time zone of the client. I really just want the time as saved in the db. – AZee Apr 22 '12 at 04:07
  • Sorry I misread. So to clarify, you have 9am in the database, and are unable to render 9am? I don't see any reason why that should be the case. Can you paste the code you use to render the date to html? – Shaun Rowan Apr 22 '12 at 04:33
  • same as my comment to Nesim... (e is the datacontext) ETAStart = Convert.ToDateTime(e.ETAStart), ETAEnd = Convert.ToDateTime(e.ETAEnd)... in the view the time is displayed using the timezone of the client. – AZee Apr 22 '12 at 04:41
  • 1
    Don't know what that means. What is e.ETAStart and why does it have to be converted to a datetime, if that's not what it is? – Shaun Rowan Apr 22 '12 at 04:51
  • If 9am is in the database, and you're pulling that from the database and rendering it to the client, what mechanism are you proposing is changing the datetime? – Shaun Rowan Apr 22 '12 at 04:51
  • It is being converted otherwise the error "cannot implicitly convert System.DateTime? to System.DateTime". The reason for this post is to try and determine why the times are begin converted so I have no clue what "mechanism". It is being done automatically and I need to find a way to turn it off. – AZee Apr 23 '12 at 03:36
  • When your ORM or whatever is first pulling the data from the database, it will match. You need to identify where the conversion is taking place and provide an example of how to replicate it. Most likely you are doing something wrong, and you will figure out what that is while going through that process. You are now saying that your problem is that there is a conversion when *rendering* the datetime - yet your code sample illustrate how you save the initial value (or changes to it?). That isn't helpful. – Shaun Rowan Apr 24 '12 at 14:24