Format time in DataGridView to local timezone

Question

I have a process that runs on a SQL server that is set to Eastern time, when the process it completed we populate a table with the completion time. I have a view that is currently being used by multiple applications which displays the Date/Time of the process in different columns, since this is being used by other apps I can't change the view.

The query I use to return my process data is below and the Date/Time are being converted to a varchar on the server side - so it isn't coming to the UI as a date/time.

 SELECT CONVERT(VARCHAR(10), A2.TaskDateTime, 101) AS TaskDate,
        CONVERT(VARCHAR(5),  A2.TaskDateTime, 108) AS TaskTime
 FROM Task  AS T2
 JOIN Application_Task AS A2    
  ON A2.TaskID = T2.TaskID

This data is then being displayed in the UI via a datagridview. I am trying to format the time column in a DataGridView to my local time zone because the users will be in different time zones.

I have looked at DataGridViewCellStyle.Format and search SO and found this on converting to local time but that is parsing one string. I can't seem to figure out how to apply that to the entire column of data.

I would appreciate any help and/or direction on where to begin with this.

Answer 1

I handle this in CellFormatting event:

    private void OnCellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
    {
        if (e.RowIndex < 0 || e.ColumnIndex < 0) return;
        DataGridView dgView = (DataGridView)(sender);
        // no need to add TaskTime...
        if (e.ColumnIndex != dgView.Columns["TaskDate"].Index) return;
        string cellValue = e.Value + " " + dgView.CurrentRow.Cells[dgView.Columns["TaskTime"].Value);
        DateTime dtValue;
        DateTime.TryParse(cellValue, out dtValue);
        DateTime dtValueUTC = TimezoneInfo.ConvertTimeToUtc(dtValue, "Eastern Time Zone");
        e.Value = dtValueUTC.Value.ToLocalTime();
    }

Answer 2

Make sure all your data is UTC on the database. This is pretty much the standard way to store date/time on databases. I learned this the hard way.

Dump this code into your CellFormatting Event

private void dgv_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
    {
        if (e.Value is DateTime)
        {
            DateTime value = (DateTime)e.Value;
            switch (value.Kind)
            {
                case DateTimeKind.Local:
                    break;
                case DateTimeKind.Unspecified:
                    e.Value = DateTime.SpecifyKind(value, DateTimeKind.Utc).ToLocalTime();
                    break;
                case DateTimeKind.Utc:
                    e.Value = value.ToLocalTime();
                    break;
            }
        }
    }

Edit: I just noticed you're moving from EST to Local and noticed you can't change the database. I'm leaving the top function intact since this is a popular search result.

So add these lines to the top of the function above

if (e.ColumnIndex == 0) //change this to your column
{
    String sqlFormat = "MM/dd/yyyy"; //change this to the sql format
    DateTime parsedDateTime = DateTime.ParseExact(e.Value, sqlFormat, null);
    TimeZoneInfo tziEastern = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
    e.Value = TimeZoneInfo.ConvertTimeToUtc(parseDateTime, tziEastern);
}