CODEWITHSUNDEEP
c#asp.netsql-serverdatetimeutc
Stefan Over
Stefan Over

Reputation: 6046

Converting UTC to local time returns strange result

I have a solution of three projects:

  1. Core
  2. Outlook Add-In
  3. ASP.NET Website

Both, the Outlook Add-In and the Website use the same methods from Core project to get data from SQL Server. When I write my data into database, I convert all DateTime values of two tables into UTC time:

POLL_START              POLL_END
2013-07-31 12:00:00.000 2013-08-01 12:00:00.000

and

PICK_DATE
2013-07-31 12:00:48.000
2013-07-31 13:00:12.000

When I get the data in my Outlook Add-In, this is the correct result:

enter image description here enter image description here

When opening the same in my website, the picks are fine:

enter image description here

But my start and end time are "broken" - the offset is added, bute the wrong hours are used:

enter image description here

Here's the code for my converting, that both, Outlook and the website, use:

private static void ConvertToLocalTime(POLL item)
{
    item.POLL_START = item.POLL_START.FromUTC();
    item.POLL_END = item.POLL_END.FromUTC();
}

private static void ConvertToLocalTime(PICK pick)
{
    if (pick.PICK_DATE != null) pick.PICK_DATE = ((DateTime)pick.PICK_DATE).FromUTC();
}

And the implementation of DateTime.FromUtc():

public static DateTime FromUTC(this DateTime value)
{
    var local = TimeZoneInfo.Local;
    return TimeZoneInfo.ConvertTime(value, TimeZoneInfo.Utc, local);
}

I had the same result with DateTime.ToLocalTime(). Anyone an idea?

EDIT 1:

This is how the start and end gets displayed on the website (end with End instead of Start):

var startCell = new TableCell
        {
            Text = String.Format(
                @"<a href='{0}' title='{2}' target='_blank'>{1:dd.MM.yyyy HH:mm \U\T\Czzz}</a>",
                    Common.GetTimeAndDateHyperlink(_poll.Start, "Vote Start"),
                    _poll.Start,
                    ConvertToLocalTimeZone),
            CssClass = "InfoContent"
        };

And the picks:

answerCell = new TableCell
            {
                Text = String.Format(
                    @"<a href='{0}' title='{2}' target='_blank'>{1}</a>",
                        Common.GetTimeAndDateHyperlink(ao.Time, ao.RealAnswer),
                        ao.RealAnswer,
                        ConvertToLocalTimeZone)
            };

ao.RealAnswer returns the formated DateTime string:

return String.Format(WholeTime == true ? "{0:d}" : @"{0:dd.MM.yyyy HH:mm \U\T\Czzz}", Time);

Upvotes: 6

Views: 534

Answers (1)

Stefan Over
Stefan Over

Reputation: 6046

I solved the issue now. The DateTime values for start and end didn't get correctly converted: The values weren't casted to local time.

The reason, why the website displayed the time as local time is, that the SQL server stores every DateTime value as DateTimeKind.Unspecified instead of keeping the specified data (e.g. DateTimeKind.Utc) during the insert. When reading the data from the server, all Kinds are DateTimeKind.Unspecified, so the .ToString() of DateTime uses the local kind. This results into the UTC time + local UTC offset.

Upvotes: 1

Related Questions