How to change the date format for a date column in datatable?

Question

I fill in the data from the database. It contains two fields: DATE , TIME

Both fields: datetime column

I want to iterate through datatable and change the date format for a DATE column ie dd/MM/yyyy

 int i = 0; string d=""; foreach (DataRow dr in dataTable.Rows) { d = dr["DATE"].ToString(); DateTime date = Convert.ToDateTime(d); d = date.ToString("dd/MM/yyyy"); dataTable.Rows[i]["DATE"] = d; i++; } 

I get the following error

The string was not recognized as a valid DateTime.

Failed to save <15/02/2015> in the DATE column. The expected type is DateTime. How can i achieve this?

Answer 1

Well, you did not tell us how you create your dataTable , but here is my thought ...

If your columns are DATE and TIME DateTime , you need to specify their DateTime . Not a string .

In the .NET Framework, the DateTime structure has no implicit format. It just has a date and time value. You get this string representation when you try to format it. Therefore, 15/02/2015 will be a string in your case, not a DateTime .

You get a FormatException from your Convert.ToDateTime method, probably because your d value was not the standard date and time format for your CurrentCulture . You can use your own date and time parsing with DateTime.ParseExact or DateTime.TryParseExact .

 string s = "15/02/2015"; DateTime dt; if(DateTime.TryParseExact(s, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out dt)) { Console.WriteLine(dt); } 

Even if you do, why do you want to store string values ​​in a DateTime column? That doesn't make any sense. Even if you do, since .Rows returns a DataRowCollection , you will probably get a Collection that has been changed; an enumeration operation may fail to fail because you are trying to modify your collection during iteration.

I suggest you create another dataTable for your string values ​​and add them to the string representation of DateTime values ​​in dd/MM/yyyy format, for example:

 int i = 0; string d = ""; var stringDataTable = new DataTable(); stringDataTable.Columns.Add("DATE", typeof(String)); stringDataTable.Columns.Add("TIME", typeof(String)); foreach(DataRow dr in dataTable.Rows) { d = ((DateTime)dr["DATE"]).ToString("dd/MM/yyyy", CultureInfo.InvariantCulture); stringDataTable.Rows[i]["DATE"] = d; i++; }