Converting datetime object to date only format in asp.net datatable

Question

I am trying to display a SQL server table in asp.net gridview. There are lot of tables in SQL server database and user selects a table and that table should be displayed in gridview. For all the datetime columns in sql server table, I need to display dates only.

I read the data by using SQLDataReader and loaded the data into a datatable. If the cell is of date type, then I am trying to convert it to date only format as shown in below image.

I tired converting the date using date.toString("MM/dd/yyyy") but unable to edit it in the datatable. The variable a in the image is showing the entire date with time. Can anyone help with the changes required to made so that only date is displayed in the date column.

I initially tried pushing variable b value to datatable but unable to succeed, then I tried to convert it to date again and tried. Nothing changed :(

edit: Here's the code

for (int i =0; i<table.Rows.Count;i++)
{
    DataRow row = table.Rows[i];                                         
    for (int j = 0; j < table.Columns.Count; j++)
    {
        string t = table.Rows[i][j].GetType().ToString();
        if (table.Rows[i][j].GetType().Equals(typeof(DateTime)))
        {
            mydate = DateTime.Parse(table.Rows[i][j].ToString());
            var b = mydate.Month + "/" + mydate.Day + "/" + mydate.Year;
            var c = DateTime.Parse(b.ToString());
            var d = c.ToString("MM/dd/yyyy");
            table.Rows[i].SetField(j, c.ToString("MM/dd/yyyy"));
            var a = table.Rows[i][j];
        }
    }                        
}

Please, consider adding [Minimal, complete and verifiable example](https://stackoverflow.com/help/mcve) for us to be able to reproduce your problem and help you. Don't post an image of your code, post the code itself. — Stormhashe, Feb 26 '19 at 20:05
Possible duplicate of [How to return only the Date from a SQL Server DateTime datatype](https://stackoverflow.com/questions/113045/how-to-return-only-the-date-from-a-sql-server-datetime-datatype) — gilliduck, Feb 26 '19 at 20:06
@gillduck User selects different tables. Some might have date columns and some might not. So I cannot select using column name. — user147504, Feb 26 '19 at 20:14
@Stormhashe, I posted the image so that people can see the values after debugging. — user147504, Feb 26 '19 at 20:16
did you try setting column format - something like - table.Columns["colname"].DefaultCellStyle.Format = "your format" — Ivan Salo, Feb 26 '19 at 20:33
Nope. All the date columns does not have same column name. Some have like Date of Purchase, Date of travel and others only Date — user147504, Feb 26 '19 at 20:48
You're using a hammer to screw in a light bulb. In other words, the wrong tool for the job. If you want to display a date in a certain way, do so when you display it, not when you get it from the database. — Heretic Monkey, Feb 26 '19 at 20:59
@HereticMonkey I got all the data into datatable and trying to edit the datatable before displaying it in gridview. — user147504, Feb 26 '19 at 21:10
... Right. You're using the wrong tool (`DataTable`) for the job. The GridView can be configured to display dates in a certain format. — Heretic Monkey, Feb 26 '19 at 21:13
@HereticMonkey I tried that before. I am unable to identify the datatype of the value in gridview cell. So I thought of converting before displaying it in gridview — user147504, Feb 26 '19 at 21:31
@HereticMonkey Everything in the gridview is shown as strings. I am unable to identify date fields. — user147504, Feb 26 '19 at 21:45
You need to look at the column's [DataType](https://learn.microsoft.com/en-us/dotnet/api/system.data.datacolumn.datatype?view=netframework-4.7.2) instead of using `GetType()`. — John Wu, Feb 27 '19 at 02:57

Canica · Accepted Answer · 2019-02-27T03:09:16.960

1

It seems like you are tackling a UI issue in your backend code. This should be tackled on the UI side without impacting your actual data. You can accomplish this by applying a format to your bound column or template:

<asp:BoundField DataField="PurchaseDate" HeaderText="Purchase Date" DataFormatString="{0:MM/dd/yyyy}" />

OR

<asp:TemplateField HeaderText ="Purchase Date" >
   <ItemTemplate >
   <asp:Label ID="lblDate" runat="server" 
              Text='<%# Eval("PurchaseDate", "{0:MM/dd/yyyy}") %>' />
    </ItemTemplate>
</asp:TemplateField>

EDIT:

@DineshInavolu why don't you try the rowdatabound event and TryParse?

void OrderGridView_RowDataBound(Object sender, GridViewRowEventArgs e)
{        
    if(e.Row.RowType == DataControlRowType.DataRow)
    {
        DateTime? dateValue = null;
        if(!DateTime.TryParse(e.Row.Cells[1].Text, out dateValue))
            e.Row.Cells[1].Text = dateValue.Value.ToString("MM/dd/yyyy");        
    }
}

edited Feb 27 '19 at 03:09

answered Feb 26 '19 at 20:52

Canica

2,650
3
18
34

I don't know the column names of the table. User will select a table name from radio button list and that table should be displayed in the gridview. If the column is of date type, then I should take action. – user147504 Feb 26 '19 at 21:06
@DineshInavolu in that case you should still handle this in the UI, for example in the gridview databound event – Canica Feb 26 '19 at 21:30
I am unable to find the datatype of the data present in the cell. Everything is shown as string type there. – user147504 Feb 26 '19 at 21:36
@DineshInavolu see edit. I hope this helps explain one approach with the gridview rowdatabound event. – Canica Feb 27 '19 at 02:58
It worked great. I got a new problem. I have an other column for time duration and it is of type time in SQL server. Even it got converted and showing as current date. – user147504 Feb 27 '19 at 14:36
@DineshInavolu what does the value of your time string look like? Please mark this as the answer if it resolved your original problem. – Canica Feb 27 '19 at 14:44
I used TimeSpan.parse() method to identify time values and not applied this date parse on those values. It is working perfect now. Thanks for your help. – user147504 Feb 27 '19 at 14:52

Converting datetime object to date only format in asp.net datatable

1 Answers1