How to format datetime column of datatable to "dd/MMM/yyyy hh:mm:ss tt" - "01/jan/2016 01:00:11 PM"

Question

i have datatable with multiple datetime columns now the datetime is formatted as dd/MM/yyyy hh:mm:ss tt which is giving me output 01/01/2016 10:00:00 AM i want to format that to dd/MMM/yyyy hh:mm:ss tt, expected output 01/Jan/2016 10:00:00 AM.

I tried this but it is not doing the conversion.

my datatable has values

    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();

                    }

Answer 1

DataTable for data storage. DataGridView for data display. So leave the data in the table unchanged. Change the format data display in the grid.

dataGridView.Columns["timestamp"].DefaultCellStyle.Format = "dd/MMM/yyyy hh:mm:ss tt";

Where "timestamp" is your column DateTime type.
Set this property after data binding.

Answer 2

The best way to change the datatype of datatable is to clone the datatable to new datatable, then the clone datatable will have only the column names and not the data. as the datatable will not have data we can change the datatype of the column and then import the data to the clone datatable, and format the datetime which will resolve the problem. sample code below:

When you are supposed bind only datagridview you can simply use:(the answer provied by another user

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]);
            }
        }
}