Formatting date format of Gridview cell

1
votes

I am confused on formatting date format in a gridview cell from (dd/MM/yyyy H:ss:mm) to (yyyy-MM-dd).

However, I haven't created any column in .aspx-page, but using dynamic binding from SQL to bind result to Gridview datasource. So, adding code which is similar bellowing is not working fine.

<ItemTemplate>
    <%#Convert.ToDateTime(Eval("StartDate")).ToString("yyyy-MM-dd") %>
</ItemTemplate>

So, is there any way to changing formatting of the date value?

2
c#asp.netdategridviewformatting
In query, its already a DATE type value. Once Gridview.databind(), date will be shown as 'dd/MM/yyyy H:ss:mm'. – Edmund
@Edmund what is the field's type in the database? If it's any of the date-related types you don't need to convert. Dates have NO format, they are binary types. The query will return them as DateTime so a simple .ToString("yyyy-MM-dd") would be enough. – Panagiotis Kanavos
@Edmund If it's text, you have a serious bug - you should use the correct type to store dates. No matter how certain you are that the strings will always look like dd/MM/yyyy someone will always enter text in a different format. It might even be you. Once that happens you won't be able to find the incorrect values - what does 04/05/2018 mean? – Panagiotis Kanavos
@KarthikeyanNagaraj string formatting in SQL is a very bad idea. The double conversion is even worse. There's no need to convert to text if the field is a date or datetime, the date values will be retrieved as DateTime values ready for display and formatting – Panagiotis Kanavos

2 Answers

0
votes

you can do it in RowDataBound event

protected void gvrequests_RowDataBound(Object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
          string strTemp= e.Row.Cells[3].Text;

        e.Row.Cells[3].Text=Convert.ToDateTime(strTemp).ToString("yyyy-MM-dd h:mm tt")
        }
   }
0
votes

Replace your binding code with :

<ItemTemplate>
    <%# Eval("StartDate", "{0:yyyy-MM-dd}") %>
</ItemTemplate>

Don't change your query, in fact make sure StartDate is returned as a date or datetime. If you convert the date field to text with CAST() or CONVERT() remove the conversion.

Explanation

If the database field is a date-related type, eg date, datetime etc it has no format. It's a binary value. When you read it, unless you explicitly convert it to a string (bad idea) it is retrieved as a DateTime value, which also doesn't have any format. Formats apply only when you convert the binary values to strings.

DataBinder.Eval has an overload that accepts a format string, similar to String.Format and returns a string.

The docs show how to use this overload to generate URL links :

<a href='<%# Eval("ProductID", "details.asp?id={0}") %>'>See Details</a>