I'm 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'd like to format that column so that instead of containing a full date, it will be formatted like "MM/DD/YYYY".
I've tried looping through each row in the table and changing the cell for that column, but I get an error saying that the string isn't a valid DateTime object.
I tried changing the column DateType to a string, but I get an error saying I can't change the DateType after the table is filled.
How can I do this? This seems like such a simple thing, but I'm having so much trouble with it.
As a work around you could create a new column and store the formatted date there:
dt.Columns.Add("DateStr");
foreach (DataRow dr in dt.Rows)
{
dr["DateStr"] = string.Format("{0:MM/dd/yyyy}", dr["OriginalDate"]);
}
Change your stored procedure to cast the field to a string using CONVERT
...
SELECT CONVERT(varchar, DateFieldName, 101) AS DateFieldNameFormatted
The 101 is the format code for mm/dd/yyyy (see link for other codes/formats)
You can't format a DateTime struct value. Don't think of it that way. It's purpose is to hold a value representing:
an instant in time, typically expressed as a date and time of day.
You can only specify the format when you convert the value of that instance to a string. Leave your DataTable schema as is. Wherever you need to format it, look at some of the formats you can use with the ToString(string) method.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With