datetime - How to change datagridview column date format when gridview has autogeneratecolumns=true



bound net (9)

I am auto generating columns in gridview depending on search parameters, few columns will be added or removed.

Please suggest me a way to set the date format to dd-mmm-yyyy for entire column in gridview.

For now, I'm doing it using rowdatabound. It checks every row, So it takes time to show the results.

This is what I do in rowdatabound

if (e.Row.RowType == DataControlRowType.DataRow)
{
        System.Data.DataRowView dtview;
        DateTime dt;
        int intCounter;
        dtview = (DataRowView)e.Row.DataItem;

        for (intCounter = 0; intCounter <= dtview.Row.ItemArray.Length - 1; intCounter++)
        {
            if (dtview.Row.ItemArray[intCounter] is System.DateTime)
            {
                dt = (DateTime)dtview.Row.ItemArray[intCounter];
                e.Row.Cells[intCounter].Text = dt.ToString("dd-MMM-yyyy");
            }
        }
    }

This checks for all records and then changes based on condition.

But I want to do it better, just by identifying the column and change the date format for complete column.


Answers

Is it possible to format a date column of a datatable?

The smartest thing to do would be to make sure your DataTable is typed, and this column is of type DateTime. Then when you go to actually print the values to the screen, you can set the format at that point without mucking with the underlying data.

If that's not feasible, here's an extension method I use often:

public static void Convert<T>(this DataColumn column, Func<object, T> conversion)
{
    foreach(DataRow row in column.Table.Rows)
    {
        row[column] = conversion(row[column]);
    }
}

You could use in your situation like:

myTable.Columns["DateOfOrder"].Convert(
    val => DateTime.Parse(val.ToString()).ToString("dd/MMM/yyyy"));

It only works on untyped DataTables (e.g. the column type needs to be object, or possibly string).


.ToString("dd/MMM/yyyy") (assuming your data is DateTime type)


How to remove the time part in Datefield of my data in gridview

if you really want to do it from a string you can use substring_index

SELECT SUBSTRING_INDEX('2016-05-01 12:00:00 AM', ' ', 1);

sample

MariaDB [yourschema]> SELECT SUBSTRING_INDEX('2016-05-01 12:00:00 AM', ' ', 1);
+---------------------------------------------------+
| SUBSTRING_INDEX('2016-05-01 12:00:00 AM', ' ', 1) |
+---------------------------------------------------+
| 2016-05-01                                        |
+---------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [yourschema]>

if you get the date from the database you can format it with DATE_FORMAT

SELECT DATE_FORMAT('2016-05-01 12:00:00 AM', '%Y-%m-%d');

sample

MariaDB [yourschema]> SELECT DATE_FORMAT('2016-05-01 12:00:00 AM', '%Y-%m-%d');
+---------------------------------------------------+
| DATE_FORMAT('2016-05-01 12:00:00 AM', '%Y-%m-%d') |
+---------------------------------------------------+
| 2016-05-01                                        |
+---------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

MariaDB [yourschema]>

On the date field in your gridview:

<asp:Label id="lblDate" runat="server" Text='<%# Eval("YourDate", "{0:yyyy/MM/dd}") %>' />

Disclaimer: I haven't tried this myself, but it looks possible.

A GridView has a public property called ColumnsGenerator that has a type of IAutoFieldGenerator. This is the object that determines how the columns are generated.

There's already an implementation of IAutoFieldGenerator out there, the default one: GridViewColumnsGenerator. This is a public, non-sealed class, and you can derive a type from it.

The method you would have to override is this one:

public override List<AutoGeneratedField> CreateAutoGeneratedFields(
      object dataObject, Control control);

Note the output, a List<T> of AutoGeneratedField. AutoGeneratedField has a property called DataFormatString:

public override string DataFormatString { get; set; }

So all you'd have to do is override CreateAutoGeneratedFields, like this:

public class MyDerivedGridViewColumnsGenerator : GridViewColumnsGenerator
{
   public override List<AutoGeneratedField> CreateAutoGeneratedFields(
      object dataObject, Control control)
   {
       var list = base.CreatedAutoGeneratedFields(dataObject, control);
       foreach(var field in list)
       {
         if(field.DataType == typeof(DateTime))
             field.DataFormatString = "dd-MMM-yyyy";
       }
       return list;
   }
}

Now, I'm not clear on how the ColumnsGenerator property gets set, so you might have to do it in code. But that should be fairly simple, since GridViewColumnsGenerator has a parameterless constructor:

 // GridView myGridView;
 myGridView.ColumnsGenerator = new MyDerivedGridViewColumnsGenerator();

I would set it before you bind to the GridView, so it's in place when it's time to create the columns.


if you are binding an DataTable to Grid then Write an extension method or link Query like

public static void ChangeDateFormat<T>(this DataColumn column, Func<object, T> conversion)
{
   foreach(DataRow row in column.Table.Rows)
   {
    row[column] = conversion(row[column]);
   }
}

And to call that Method

dataTable.Columns["DateColumanName"].ChangeDateFormat(
val => DateTime.Parse(val.ToString()).ToString("dd/MMM/yyyy"));

Actual Source code pulled from here
And also note you need check the existence of column and data type and the other checks to get rid of errors.

Hope it helps.


You can specify format in Eval expression on the label or column in the gridview. Below are two examples. One in a BoundField and the other in a TemplateField. Choose whichever suits. Add your other fields as needed.

        <asp:GridView ID="GridViewdoc" DataKeyNames="OrderId" AutoGenerateColumns="false" runat="server">
            <Columns>
                <asp:BoundField HeaderText ="Date" DataField="Dateordered" DataFormatString="{0:yyyy/MM/dd}" />
                <asp:TemplateField HeaderText="Date" >
                    <ItemTemplate>
                         <asp:Label id="lblDate" runat="server" Text='<%# Eval("Dateordered", "{0:yyyy/MM/dd}") %>' />
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>

edit1:

You should to alter the format of a column of a GridView from CodeBehind Add a RowDataBound to your grid view.

Then in the GridView1_RowDataBound(object sender, GridViewRowEventArgs e) method, you'll be able to access e which will provide you with access to the individual cells of that row where you can specify a formatter. (see this)

edit2: In this edit I change datetime to date. I have a student table in database and Gridview1 show it to the following:

Last column is datetime. For change format of this, I used this code:

<script runat="server">
void GridView1_RowDataBound(Object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        e.Row.Cells[3].Text = String.Format("{0:dd/MM/yyyy}", Convert.ToDateTime(e.Row.Cells[3].Text));
    }
}
</script>

After do it, the result is shown below:


Just expounding on saille's answer here:

For a DateTime, format isn't an issue. A DateTime is actually the number of ticks counting up from midnight, January 1, 1 A.D. So, really, it's just a long. Formatting only becomes an issue when it comes time to convert it into a string. So, you'll have to go and take care of the formatting either when you pull it out of the data table and are ready to output it, or put it into the data table as a string (which I would not recommend, for flexibility purposes). The formatting can be done with the .ToString call on the DateTime that saille suggests, .ToString("dd/MMM/yyyy")


Don't repeat yourself - automate date conversion using $.parseJSON()

Answers to your post provide manual date conversion to JavaScript dates. I've extended jQuery's $.parseJSON() just a little bit, so it's able to automatically parse dates when you instruct it to. It processes ASP.NET formatted dates (/Date(12348721342)/) as well as ISO formatted dates (2010-01-01T12.34.56.789Z) that are supported by native JSON functions in browsers (and libraries like json2.js).

Anyway. If you don't want to repeat your date conversion code over and over again I suggest you read this blog post and get the code that will make your life a little easier.