converting a date from SQL Server to C# and then to javascript gives different results on US vs. London webserver

Question

I have an asp.net mvc calendar application (using jquery ui datepicker) and i am running into a weird situation where when i test on a US webserver i see a certain date and when i test on a London web server i see a different date (the date before)

Here are the details:

I am storing a date in sql server as:

 2010-09-16 00:00:00.000

I am then loading that into C# DateTime object.

I need to pass that down as part of a json object to my clientside javascript so i was suggested this solution:

jsonobject.Date = UnixTicks(a.Date),

where UnixTicks is:

private static double UnixTicks(DateTime dt)
    {
        DateTime d1 = new DateTime(1970, 1, 1);
        DateTime d2 = dt.ToUniversalTime();
        TimeSpan ts = new TimeSpan(d2.Ticks - d1.Ticks);
        return ts.TotalMilliseconds;
    }

I am then converting it to a javascript date by using this code below on the client side:

var d = new Date(jsonobject.Date);

does anyone know why given a US server or a London server i would get back a different date. At first, i thought it was the

 DateTime d2 = dt.ToUniversalTime();

but i changed that to:

 DateTime d2 = dt;

and still saw the same issue. (on London web server date would show up 1 day prior to the US web server)

any suggestions?

Answer 1

If you're storing local time in the database instead of UTC, then you could get different dates back when converting back to local time. Make sure your dates are converted to UTC before you commit them to the database. If you want everyone to always see the same date no matter where they are, always return the UTC date- do not convert back to local time.

Answer 2

It's definitely a localization issue. You need to store your DateTime in the same Time Zone and account for different Date formatting. For instance, September 1st, 2010 in the US is formatted "9/1/2010" and in the UK it's formatted "1/9/2010". If you're looking at that as a US date, you're going to be looking at January 9th, 2010.

Answer 3

if you just want to completely ignore the time zone and localization issue, just present the dates as strings to your application:

SELECT
    CONVERT(varchar(10),YourDatetimeColumn,111) AS StringDate1  --YYYY/MM/DD
   ,CONVERT(varchar(10),YourDatetimeColumn,121) AS StringDate2  --YYYY-MM-DD
    FROM ...

and then just use them within the application like regular string data and they will not change or be adjusted for you.