我有一个包含多个日期时间列的数据表,现在日期时间的格式为dd/MM/yyyy hh:MM:ss tt,这将为我提供2016年1月1日10:00:00 AM的输出。我想将其格式设置为dd/MMM/yyyy hh:MM:ss tt,预计输出为2016年1日10:0:0 AM。
我试过了,但它没有进行转换。
我的数据表有值
timestamp
06/01/2016 1:32 PM
06/01/2016 3:33 AM
07/01/2016 4:42 AM
string query = "SELECT t1.[timestamp] FROM [REPORT] t1";
//I should not change this because i am not supposed to give alias name to column as per requirement
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(query, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
DataTable dt = new DataTable();
dt=ds.Tables[0];
//dt REsult is
//2016-01-06 03:33:27.810
//2016-01-06 03:33:27.810
// expected result is
//2016/Jan/06 03:33:27.810
//2016/Jan/06 03:33:27.810
foreach (DataColumn dcol in dt.Columns)
{
if (dcol.DataType == typeof(DateTime))
{
dt.Columns[dcol.ColumnName].Convert(val => DateTime.Parse(val.ToString()).ToString("dd/MMM/yyyy"));
}
}
dataGridView1.DataSource = dt;
dataGridView1.BindingContext = new BindingContext();
}
DataTable
用于数据存储。数据显示的DataGridView
。因此,保持表中的数据不变。更改网格中显示的数据格式。
dataGridView.Columns["timestamp"].DefaultCellStyle.Format = "dd/MMM/yyyy hh:mm:ss tt";
其中"timestamp"是您的列DateTime
类型
在数据绑定后设置此属性。
更改数据表数据类型的最佳方法是将数据表克隆到新的数据表,然后克隆的数据表将只有列名而没有数据。由于数据表没有数据,我们可以更改列的数据类型,然后将数据导入克隆数据表,并格式化日期时间,这将解决问题。下面的示例代码:
当你应该只绑定数据网格视图时,你可以简单地使用:(另一个用户提供的答案
dataGridView.Columns["timestamp"].DefaultCellStyle.Format = "dd/MMM/yyyy hh:mm:ss tt";
string query = "SELECT t1.[timestamp] FROM [REPORT] t1";
//I should not change this because i am not supposed to give alias name to column as per requirement
using (SqlConnection con = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(query, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
DataTable dt = new DataTable();
dt = ds.Tables[0];
//dt REsult is
//2016-01-06 03:33:27.810
//2016-01-06 03:33:27.810
// expected result is
//2016/Jan/06 03:33:27.810
//2016/Jan/06 03:33:27.810
//foreach (DataColumn dcol in dt.Columns)
//{
// if (dcol.DataType == typeof(DateTime))
// {
// dt.Columns[dcol.ColumnName].Convert(val => DateTime.Parse(val.ToString()).ToString("dd/MMM/yyyy"));
// }
//}
DataTable dtClone = new DataTable();
List<string> colNameDateTime = new List<string>();
dtClone = mysource.Clone();
foreach (DataColumn dcol in dtClone.Columns)
{
if (dcol.DataType == typeof(DateTime))
{
colNameDateTime.Add(dcol.ColumnName.ToString());
dtClone.Columns[dcol.ColumnName].DataType = typeof(string);
}
}
//foreach (DataColumn dcol in dtClone.Columns)
//{
foreach (DataRow dr in mysource.Rows)
{
dtClone.ImportRow(dr);
}
//}
foreach (DataColumn dcol in dtClone.Columns)
{
if (colNameDateTime.Count > 0)
{
for (int i = 0; i < colNameDateTime.Count; i++)
{
if (colNameDateTime[i] == dcol.ColumnName.ToString())
{
dtClone.Columns[dcol.ColumnName].Convert(val => DateTime.Parse(val.ToString()).ToString("dd/MMM/yyyy hh:mm:ss tt"));
}
}
}
}
dataGridView1.DataSource = dtClone;
dataGridView1.BindingContext = new BindingContext();
}
static class ExtensionHelper
{
public static void Convert<T>(this DataColumn column, Func<object, T> conversion)
{
foreach (DataRow row in column.Table.Rows)
{
row[column] = conversion(row[column]);
}
}
}