LocalTime to UTC keeping DST

Question

I have a datagrid that is sorted using a DateTime column that comes from a UTC value converted to local time. the problem is with DST as there is 1 hour in the year that will be repeated (from Nov 6, 2:00:00 AM goes back to 1:00:00 AM). i already implement a way to compare the column using a class that inheriths from IComparable and manually compares dates converting them again using ToUniversalTime() but it returns wrong values. to explain better let me show an example:

        DataTable table = new DataTable();
        table.Columns.Add("UTC Date", typeof(DateTime));
        table.Columns.Add("Local Date", typeof(DateTime));            
        table.Columns.Add("UTC From Local", typeof(DateTime));
        dataGridView1.DataSource = table;
        DateTime aux;

        DataRow newRow = table.NewRow();
        aux = new DateTime(2016, 11, 06, 06, 30, 0, DateTimeKind.Utc);
        newRow["UTC Date"] = aux;
        newRow["Local Date"] = aux.ToLocalTime();
        newRow["UTC From Local"] = Convert.ToDateTime(newRow["Local Date"]).ToUniversalTime(); table.Rows.Add(newRow);

The values displayed will be:

UTC Date: 11/06/2016 6:30 AM

Local Date: 11/06/2016 1:30 AM

UTC From Local: 11/06/2016 7:30 AM

As you can see the column "UTC From Local" is wrong or at least i would expect 6:30 (considering DST) and not 7:30 (without DST).

Any help?????

Answer 1

You have to keep in mind the DateTimeMode property of DataColumn. If you create a new DataColumn it is set to Unspecified. But in your use case you want Utc and Local

var table = new DataTable();
table.Columns.Add("UTC Date", typeof(DateTime)).DateTimeMode = DataSetDateTime.Utc;
table.Columns.Add("Local Date", typeof(DateTime)).DateTimeMode = DataSetDateTime.Local;
table.Columns.Add("UTC From Local", typeof(DateTime)).DateTimeMode = DataSetDateTime.Utc;

var newRow = table.NewRow();
var aux = new DateTime(2016, 11, 06, 06, 30, 0, DateTimeKind.Utc);
newRow["UTC Date"] = aux;
newRow["Local Date"] = aux.ToLocalTime();
newRow["UTC From Local"] = Convert.ToDateTime(newRow["Local Date"]).ToUniversalTime();