CODEWITHSUNDEEP
c#datetimedatagridviewdatatable
Hank
Hank

Reputation: 2646

Formatting a datagridview column to display formatted datetime instead of serial datetime

I load a DataTable with the results of a SQL query, such as 'Select * From Table'. A few of the columns are a date/time stamp in serial and is regarded in the DataTable as a double, e.g. 20170613103015, These columns are also have "Date" in the column name.

After this I set a DataGridView data source as the DataTable, what I am wondering is, if I can then format these date/time stamp as an actual datetime, if so how?

I realize that this doesn't work but at least shows what the format I'm after is.

dgvAssets.DataSource = rowData.asset_table;

for (int c = 0; c < dgvAssets.Columns.Count; c++)
{
    if (dgvAssets.Columns[c].Name.Contains("DATE") && dgvAssets.Columns[c].ValueType == typeof(double))
    {
        dgvAssets.Columns[c].DefaultCellStyle.Format = "dd/MM/yyyy HH:mm:ss tt";
    }
}

Upvotes: 0

Views: 3315

Answers (4)

Alexander Petrov
Alexander Petrov

Reputation: 14261

I can offer another method, completely different from those already proposed.

Do the data conversion in the sql query.

Instead of a select * query write the query, indicating all columns. During which converting the necessary columns to datetime.

Something like this:

select id
     , convert(datetime, format(SomeDate1, '####-##-## ##:##:##'))
     , convert(datetime, format(SomeDate2, '####-##-## ##:##:##'))
from Table

Assuming you are using Sql Server.

Upvotes: 1

jdweng
jdweng

Reputation: 34433

Here is how to fix the datatable :

DataTable asset_table = rowData.asset_table;

int colCount = asset_table.Columns.Count;
for (int col = colCount - 1; colCount >= 0; colCount--)
{
    string colName = asset_table.Columns[col].ColumnName;
    if (colName.Contains("DATE"))
    {
        asset_table.Columns.Add("Temp Col", typeof(DateTime));
        foreach (DataRow row in asset_table.AsEnumerable())
        {
            DateTime date = DateTime.ParseExact(((double)row[colName]).ToString(), "yyyyMMddhhmmss", CultureInfo.InvariantCulture);
            row["Temp Col"] = date;
        }
        asset_table.Columns.Remove(colName);
        asset_table.Columns["Temp Col"].ColumnName = colName;
        asset_table.Columns[colName].SetOrdinal(col);
    }
}

Upvotes: 2

dovid
dovid

Reputation: 6491

use the CellFormatting event to manuplate value for view. first collect the relevant column indexs, then add CellFormatting event (i did it a lambda to capture colsDate variable)

dgvAssets.DataSource = rowData.asset_table;

int[] colsDate = dataGridView1.Columns
                              .Cast<DataGridViewColumn>()
                              .Where(col => col.Name.Contains("DATA"))
                              .Select(col => col.Index)
                              .ToArray();


dataGridView1.CellFormatting += (s, e) =>
{
    if (colsDate.Contains(e.ColumnIndex))
    {
        var date = DateTime.ParseExact(e.Value.ToString(), "yyyyMMddhhmmss", CultureInfo.InvariantCulture);
        e.Value = date.ToString("dd/MM/yyyy HH:mm:ss tt");
        e.FormattingApplied = true;
    }
};

//this its necessary if you want allow user edit the cell, and take the value back to db double format.

dataGridView1.CellParsing += (s, e) =>
{
    if (colsDate.Contains(e.ColumnIndex))
    {
        var date = DateTime.Parse(e.Value.ToString());
        e.Value = double.Parse(date.ToString("yyyyMMddhhmmss"));
        e.ParsingApplied = true;
    }
};

Upvotes: 1

Pablo notPicasso
Pablo notPicasso

Reputation: 3161

Use CellFormating event:

    private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
    {
        if (e.ColumnIndex == Datecolumn.Index && e.Value !=null)
        {
            e.Value = DateTime.ParseExact(Convert.ToInt64(e.Value).ToString(), "yyyyMMddHHmmss", null).ToString("dd/MM/yyyy hh:mm:ss tt");
            e.FormattingApplied = true;
        }
    }

Formatting double to DateTime is an example. I do not know if this is your format of date. You can also modify condition e.ColumnIndex == Datecolumn.Index so it would be good for all of your columns (e.g. check if index in in some kind of array/list of valid indexes)

Upvotes: 1

Related Questions