Changing a DataType of a column in a DataTable from DateTime to String

Question

I am loading data from my database into a DataTable, and one of the columns is a date field.

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)) { SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "MySP"; cmd.CommandType = CommandType.StoredProcedure; conn.Open(); using (SqlDataReader rdr = cmd.ExecuteReader()) { dt.Load(rdr); } } 

I would like to format this column so that instead of containing the full date, it will be formatted as "MM / DD / YYYY".

I tried scrolling every row in the table and changing the cell for that column, but I get an error that the row is not a valid DateTime object.

I tried changing the DateType column to a row, but I get a message stating that I cannot change DateType after populating the table.

How can i do this? It sounds like such a simple thing, but I have so many problems with it.

Answer 1

As work around, you can create a new column and save the formatted date there:

 dt.Columns.Add("DateStr"); foreach (DataRow dr in dt.Rows) { dr["DateStr"] = string.Format("{0:MM/dd/yyyy}", dr["OriginalDate"]); } 

Answer 2

Modify your stored procedure to pass a field to a string using CONVERT ...

 SELECT CONVERT(varchar, DateFieldName, 101) AS DateFieldNameFormatted 

101 is the format for mm / dd / yyyy (see link for other codes / formats)

Answer 3

You cannot format the value of a DateTime struct . Do not think of it like that. The goal is to hold onto a value representing:

instant time, usually expressed as the date and time of day.

You can specify the format only when converting the value of this instance to a string. Leave your DataTable schema as is. Wherever you need to format it, look at some formats that you can use with ToString (string) .