TLDR; the code below converts a DataTable’s time columns from UTC to local time, even when the DataTable is populated with data.
My utility framework for general data displays queries a predefined content-specific DataTable into a DataGridView. The columns are automatically handled by the framework. The data and any search parameters are passed in context on a Content object. This provides a way to quickly provide task-specific data analysis and manipulation tools that complement our MES.
The ISV’s database contains dates in both UTC and local format. While this can usually be explained away, my current project uses dates of both local and UTC time. I needed to display all dates in local time, so I needed a way to convert from UTC to local time. Vanilla SQL Server does (did?) not have such functionality, but the .Net framework used by the client has a variety of tools. Besides, client-specific time zone conversions really should occur on the client instead of the server, since the client can be in a different time zone than the server. All of this means I needed to get between the populated DataTable and DataGridView and change the time zone or at least the time value.
The major complication is that you cannot change a DateTimeMode of a column if the DataTable is populated. Since my DataTable columns do not exist outside of the data, I needed to 1) clone the structure to a new DataTable, 2) add a new column to represent local time, 3) convert the datetime values, 4) replace the old UTC column with the new local time column. This is the method I used:
using System.Data.DataSetExtensions; // per Vassek
// using System.Data; // in the original code
/// <summary>
/// Given a DataTable and a column name of a DateTime field,
/// Convert the column datetime from UTC time (aka GMT, aka Greenwich Mean Time)
/// into local time.
/// Usage:
/// DataTable dt = dppi.Query();
/// dt = MesQueries.ConvertDTColumnUtcTimeToLocalTime(dt, “wip_start_time”);
/// dt = MesQueries.ConvertDTColumnUtcTimeToLocalTime(dt, “wip_end_time”);
/// dgv.DataSource = dt; // dgv is DataGridView.
/// dt = null;
/// </summary>
/// <param name=”dt”>DataTable with UTC column.</param>
/// <param name=”columnName”>Name of the UTC column.</param>
/// <returns>DataTable with local time column.</returns>
public static DataTable ConvertDTColumnUtcTimeToLocalTime(DataTable dt, string columnName)
{
DataTable dtClone;
try
{
// Clone the DataTable structure; not the data.
dtClone = dt.Clone();
// Add a new column to contain local time.
It is essential for a therapist to be skilled in the therapeutic use of levitra online pharmacy forgiveness to correctly assess the clients openness and readiness in terms of timing, development and religion. It helps to gain cialis 100mg canada fuller and stronger erection. One cannot ignore the fact that every men suffers from impotency at one point in his life but what mainly matters is that how a person does actually gets through the issue and how fast he tends to levitra online australia do so. The following have been overnight cialis soft well wide used traditionally to treat joint pains: Amgesic: Arthritis-tablets and Capsules: Amgesic is an anti-arthritic herbal formula which not only gives a monopoly to manufacturers inside the US. // Add a new column to contain local time.
string columnNameLocaltime = columnName + “Localtime”;
dtClone.Columns.Add(columnNameLocaltime, typeof(DateTime)).DateTimeMode = DataSetDateTime.Local;
// Row-wise copy data into new DataTable.
for (int i = 0; i < dt.Rows.Count; i++)
{
dtClone.ImportRow(dt.Rows[i]);
// Convert the old UTC time into local time. Skip nulls.
Object dateObj = null;
dateObj = dtClone.Rows[i].Field<Object>(columnName);
if (dateObj != null)
{
DateTime dateToConvert = dtClone.Rows[i].Field<DateTime>(columnName);
dtClone.Rows[i][columnNameLocaltime] = dateToConvert.ToLocalTime();
}
}
// Remove the UTC column, then replace it with the local time column.
int utcColumnOrdinal = dtClone.Columns[columnName].Ordinal;
dtClone.Columns.Remove(columnName);
dtClone.Columns[columnNameLocaltime].SetOrdinal(utcColumnOrdinal);
dtClone.Columns[columnNameLocaltime].ColumnName = columnName;
}
catch (Exception e)
{
throw new Exception(“MesQueries.ConvertDTColumnUtcTimeToLocalTime() was unable to convert the column “ + columnName + ” to local time because “ + e.Message);
}
return dtClone;
}
