DateTime issues with Azure c#, javascript, sql....so confused.

Alright, so my website allows people to store their work schedules. They enter their shift starting datetime and ending datetime. Simple enough, right?

The idea is I can look at the site and see Bob's working in Chicago tonight from 4-6pm, and Joe's working in New York from 8-10pm. And that's how I want the dates displayed in the app. If I'm viewing the site from California, I still want to display Chicago times for Bob, and New York times for Joe. I do NOT want to convert those times to California times where it'd show Bob working from 2-4pm, and Joe working from 5-7pm.

My site has sample data and when everything's located in the same timezone it's fine, but I've now deployed it to Azure servers in North Central US (It uses SQL Azure located there as well, although I'm unclear on where there is), and now the date and times are all off (jumped forward by 7 hours).

I believe the reason for this is that I use a lot of Ajax and JSON, and JSON requires datetimes to be formatted like this /Date(1240718400000)/, where the number represents the milliseconds since epoch/Unix time. I display the schedules in a Calendar/Week/or Day view, which requires a lot of javascript datetime comparisons.

Anyway, anyone know the correct way to do what I need to do? I'm thinking I need to store the datetimes as UTC, but I also need to know the timezone where the user's work shift will take place, and then I need to adjust it on the C# side, but then do I need to do anything on the javascript side?

I'm just confused, seems like too many variables to wrap my head around at the moment.

Re: DateTime issues with Azure c#, javascript, sql....so confused.

I would transfer as strings and handle transformation from string to date on javascript browser.

Re: DateTime issues with Azure c#, javascript, sql....so confused.

Thanks for the response. I think that may work. However, since I also have logic in stored procedures dealing with the shift date times, it would be nice to store it as a date still.

I think I came up with a solution, but don't know how to implement it yet. What if I just treated everything as a UTC date. By that I mean, if a user in New York says they work at 2pm on June 1st, I treat it as if the user, the webserver, the database and everything actually exists in the UTC timezone. So the datetime that's stored in the database is 2012/06/01 14:00 UTC and no conversion or timezone adjustment is necessary anywhere, and all my web and database code would always be dealing with UTC dates, so a user in California would see that same datetime as 2012/06/01 14:00 as well, which is what I want.

I think that would work, but then the question becomes, how do I force the datetime they enter to be treated as if it was entered by a user/system in the utc timezone?

I thought I had it, with the SpecifyKind method:

DateTime dt = DateTime.SpecifyKind(DateTime.Parse(dateEnteredStr), DateTimeKind.Utc);

But this doesn't seem to work. The underlying value of the DateTime object doesn't change. I was hoping this would take a date like "2012/06/01 14:00 EST" and make it "2012/06/01 14:00 UTC", but nope. There has to be a way to do this I think.

Re: DateTime issues with Azure c#, javascript, sql....so confused.

Oops, my mistake, the problem I have isn't on the C# side (the SpecifyKind function does what I want it to do), it's on the javascript side. I need to somehow create a javascript Date object similar to how I described C#'s SpecifyKind function works.

Since I'm dealing with JSON, I need javascript that will create a Date object in the UTC timezone from a given "milliseconds since epoch" value. The problem is that javascript dates automatically convert a UTC datetime value to a local timezone value.

Hopefully this code and comments will explain the problem:

// 1335834000000 = Tue, 01 May 2012 01:00:00 GMT

// The following javascript running on my laptop in California will produce a Date object with a value of
// Mon Apr 30 2012 18:00:00 GMT-0700 (Pacific Daylight Time)

var dt = new Date(1335834000000);

// I need something like this that produces the original UTC time of
// Tue, 01 May 2012 01:00:00 GMT

var dt = new Date(1335834000000, "UTC")

Re: DateTime issues with Azure c#, javascript, sql....so confused.

Alright, figured it out. Had to change a lot of javascript to make all the Date objects use the UTC timezone, but it works.

Thanks.

Re: DateTime issues with Azure c#, javascript, sql....so confused.

Hi,

i am facing the same problem , when my website access from diffrent PC with diffret time zone the JS Ajax return date is chage to the local time zone, i want to stop it & want to show the real time which come from db, can any one help me out . PLZ

Re: DateTime issues with Azure c#, javascript, sql....so confused.

What I did was treat the date as UTC at every layer (javascript front-end, C# middle-tier, and SQL backend).

The javascript code is a little tricky, but I did it with the following function which will convert ASP.Net JSON dates, as well as normal date objects and formatted date strings to UTCDates. It also will return a new UTC date for the current datetime if no param is passed. So I pass all my sending of user-entered form date fields through this function before passing it to the C# middle tier via Ajax WebService call. And also pass all my retrieved dates via Ajax/JSON from the WebService through it before displaying to the user. And finally, I use it to get the current date (treated as a UTC date) when doing any javascript logic needing to compare against the current datetime.

The C# side is easy. I pass the date object or string through the following 2 functions when sending/receiving dates to the database:

public static DateTime ConvertDateTimeToUTC(string dtStr)
{
return DateTime.SpecifyKind(DateTime.Parse(dtStr), DateTimeKind.Utc);
}

public static DateTime ConvertDateTimeToUTC(DateTime dt)
{
return DateTime.SpecifyKind(dt, DateTimeKind.Utc);
}

As dates are passed to the database, they will already be UTC dates, so I just modified any of my stored procedure logic doing date comparisons against the current date to use getUTCDate() instead of getDate()

Hope that helps.