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 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
– Stormhashe, Commented Feb 26, 2019 at 20:05
Possible duplicate of How to return only the Date from a SQL Server DateTime datatype — gilliduck
– gilliduck, Commented Feb 26, 2019 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
– user147504, Commented Feb 26, 2019 at 20:14
@Stormhashe, I posted the image so that people can see the values after debugging. — user147504
– user147504, Commented Feb 26, 2019 at 20:16
did you try setting column format - something like - table.Columns["colname"].DefaultCellStyle.Format = "your format" — Ivan Salo
– Ivan Salo, Commented Feb 26, 2019 at 20:33

Canica · Accepted Answer · 2019-02-27 03:09:16Z

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, 2019 at 3:09

answered Feb 26, 2019 at 20:52

Canica

2,7484 gold badges22 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user147504 Over a year ago

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.

Canica Over a year ago

@DineshInavolu in that case you should still handle this in the UI, for example in the gridview databound event

user147504 Over a year ago

I am unable to find the datatype of the data present in the cell. Everything is shown as string type there.

Canica Over a year ago

@DineshInavolu see edit. I hope this helps explain one approach with the gridview rowdatabound event.

user147504 Over a year ago

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.

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related