Home » VB.Net

How to show only date in DataColumn of Datatable?


I am populating one DataGridView in C# windows application. I have one DataTable in which I am displaying dates which I retrieve from database. My problem is, dates that I am getting from database includes time information also. For example, Jan 2003 12:00:00 AM ;

I want to show only Jan 2003 part of that date in cells of datatable. I tried to change the culture information of current thread, but it didnt worked. Is there any other way to achieve this?

I am using MySql as my database and there I have set datatype of this column as DATE. Then also i am getting this time information. Can anyone please enlighten on this behaviour of MySql?

Thanks and Regards,


8 Answers Found


Answer 1

I Solved the problem. Used date_format() function of MySql. It works perfectly.

Here is the link which gives more information  about this function.




Answer 2

Hi Chandrashekhar,

it can be done by formating check this link for detail




Answer 3

when you populate column of dataGridView do the following:

dataGridView1.Rows[row].Cells[column].Value = Convert.ToDateTime(dr["DateColumn"]).ToString("MM.yyyy");


Answer 4

dataGridView1.Columns[column].DefaultCellStyle.Format = "MMM yyyy";

Answer 5

Hi shekhar9448,

   Welcome to MSDN forums! I'm glad to see your active participation and discussion in MSDN forums. Based on your issue I'll suggest you 2 good choice.

  1. Use mysql date  function to convert after fill date in datatable. (please refer to http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format)

  2. Use DataGridView cell format (http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/thread/bb5ef524-b2ba-4420-82ac-230d156695aa/)

Hope this will help you!  

   If you still have any doubt and concern about this issue, please let me know. If I misunderstood you, please kindly elaborate your question.

This response contains a reference to a third party World Wide Web site. Microsoft is providing this information  as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

Please Mark as Answered If this is helpful Or Un-Mark as Answered if it is not helpful.

Best Regards,
Yan Jun
Microsoft Online Community Support


Answer 6

Thanks Yan Jun for help.

I have solved the problem  by using MySql date_format() function.

Thanks and Regards, Chandrashekhar

Answer 7




hey killer,

thats my post on stackoverflow :)

thanks for help.


Answer 8

Hi Shekhar9448,

  You are welcome I'm pleased it helped



Dear All,

We are using Dynamics CRM 4.0. We have Date on the Screen is showing is different than the CRM DB. For example Date is showing on the Screen as 28-Oct-2010. But in DB storing as 27-Oct-2010. We are facing lot of problems becoz of this since this date is critical. Anybody got the same problem ? Kindly provide the solution if anybody solved..

Thanks in Advance,




I've to dev a report, with two dates showing by user between this two dates how many activities, opportunity, contacts and accounts has been modified. I don't know if this is possible?

So it would be somethingl like :

TimeFrame : from mm/dd/yy til mm/dd/yy

UserName                                       TOTAL                    Changed records during timeframe

        * Activities                                 530                       1

        * Opportunities                           120                       5

        * Contacts                                  660                      25

        * Accounts                                  10                       1

So my worry here is how can I know how many updates has been done during this timeframe?

Is there any log or any table or I don't know?

Thanks a lot for your help,




My Data was created by retrieving ODBC data - using PowerPivot for Excel 2010, and has Birthdate defined as a Data Type = "Date".  This was uploaded to SharePoint, and a New Dataset was created to point at it.

I'm using Microsoft SQL Server 2008 R2 Report Builder 3.0.  I tried to filter this data on Birthdate, but when I go into the Filter, and specify the field that was defined as DataType = "Date" in PowerPivot - Report Builder changes the type to "Text", and gray's out the field so that I can't change it to "Date".

If I create an Expression as Cdate(Birthdate.Value), it lets me change the Data Type to "Date", and works appropriately, but we don't want to have to do this with every date field.

Why doesn't Report Builder see this field as a Date ? 


As suggested by another author's post, here are as many details as possible:

Business Goal: I want a date attribute that I created on an entity to be displayed in a different date format when it is used for a Mail Merge.

Reproduced Steps: I created an entity.  Then, I started a Mail Merge using the attributes in that entity, specifically <<Modified On>>.  When the Mail Merge document is created after having walked through the steps, the date appears, but it appears in a format different from what I would like.  For example, it shows up as "9/28/10 4:15PM", but I want it to be formatted as "September 28, 2010" (like it would in a letter).

Any idea as to how I could fix this date?  Thanks!


Article date showing previous date(1 day before actual article date) in sharepoint field in layout pages.

But in cqwp it shows correct date. Does anybody has this issue?



I'm using the dataset designer to try and generate a default value for a date/time column in a table.

How do I set the default value to be the current date/time?  The value currently reads <DBNull>.  I've tried date, = date, date.today, =date.today and I get a message "String was not recognized as a valid date/time"

Thank you!


If have a DataGrid bound to a DataTable:

dgr.ItemsSource = _das.Tables(0).DefaultView

How can I make to DataGrid display the RowErrors the DataTable may have after an update to the database? The Windows Forms DataGridView does this by default.



I have a webservice, which loads data from a database and should display this data in the DataGrid of my Silverlight 4 Application.

The problem is, that there is no support for System.Data in the Silverlight Framework so I have to make a workaround...

The columns in my database are variable, so I can't make a class because I don't know how many and which columns the DB has.

Best regards



I want to show chart along with dataTable in ASP.net 

Here is the image of what i want to do 

how can i do this in asp.net using MS chart controls



I have a datatable with queries on datadapter, my qury is like this

SELECT first_name As [First Name] FROM basic_info

This query run fine, but it is showing two colomns as result (Old column name and new column name),

first_name  |  First Name

abc            |  abc

bcd            |  bcd


I have attached the resulted table with gridView and both columns are also showing in gridview. I have 19 columns, threrefore its very hard for me to remove each column by Column.Remove("ColumnName") method.

Is there any way to remove named columns....? 


I am a novice LINQ user, and am only just beginning to get a grasp of C#.  I have a DataTable (built entirely in code) with columns, ItemID, ItemDescription, Date, Quantity (plus others).  I need to create a view or new table with the same columns, where the quantities are summed for each date - ie so I only get a single row for each different item and date in the original table.  I can see several responses which suggest using LINQ for this kind of grouping operation, but they would only seem to work where I only have one Item. I cannot make the jump to what I need.  Can anyone show me how to do this please?

Thanks in anticipation.

  In my datatable i have 1500 records, if i send 1500 records to WCF, i am facing following problem,The remote server returned an unexpected response: (400) Bad Request. To Avoid this error, i plan to send each 500 records to  WCF. so how i can split each 500 records to one datatable. Please help me, it will be very help full to me


I have a large table ("Person") with about 120 columns. I made a dataset from it with the SQL statement "SELECT * FROM PERSON". I see that the datatable contains all the columns that I want to have. However, when I run the following code to write it into a file, I only see a small part of the columns in the generated XML file:


I also tried ds.GetXml(), but the result was the same. Is there a limitation in the number of columns for DataSet.GetXML/DataTable.WriteXML?




I'm sure this is a ridiculously newbie question, but I'm having a lot of trouble with it and have spent hours wrestling and Googling this problem so I'm finally going to ask for help.

I have a DataTable of available time slots.  I have another DataTable of reserved time slots.  I need to remove from the list of available slots the ones that have been reserved.  The blocks are in 15 minute increments, but one of my problems is that the reservation can be longer than 15 minutes.  I've had some luck removing one or two, but not all of the required columns. 

Here's my code (it doesn't work right now).  Any help would be greatly appreciated!

private void GetAvailableTime()
            Common common = new Common();
            DataTable copyAvailableTime = AvailableTime();
            DataTable availableTime = AvailableTime();
            DataTable reservedTime = ReservedTime();

            StringBuilder sb = new StringBuilder();

            copyAvailableTime.DefaultView.Sort = "startTime";
            foreach (DataRow dr in reservedTime.Rows)
                copyAvailableTime.DefaultView.RowFilter = string.Format("startTime >= '#{0}#' AND endTime <= #{1}#", dr["StartTime"].ToString(), dr["EndTime"].ToString());

                foreach (DataRow dr2 in copyAvailableTime.DefaultView.Table.Rows)

            string[] rowNums = sb.ToString().Split(',');
            foreach (string rowNum in rowNums)
                if (rowNum != "")

            GridView1.DataSource = availableTime;

            GridView2.DataSource = reservedTime;

        private DataTable AvailableTime()
            DataTable AvailableTime = new DataTable();
            DateTime startTime = DateTime.Parse("10/23/2010 10:00:00");

            DataColumn myDataColumn;

            myDataColumn = new DataColumn();
            myDataColumn.DataType = Type.GetType("System.Int16");
            myDataColumn.ColumnName = "ID";

            myDataColumn = new DataColumn();
            myDataColumn.DataType = Type.GetType("System.DateTime");
            myDataColumn.ColumnName = "startTime";

            myDataColumn = new DataColumn();
            myDataColumn.DataType = Type.GetType("System.DateTime");
            myDataColumn.ColumnName = "endTime";

            DataRow myRow;

            for (int i = 0; i < 24; i++)
                myRow = AvailableTime.NewRow();

                myRow["ID"] = i;
                myRow["startTime"] = startTime;
                myRow["endTime"] = startTime.AddMinutes(15);
                startTime = startTime.AddMinutes(15);


            return AvailableTime;

        private DataTable ReservedTime()
            DataTable ReservedTime = new DataTable();
            DataColumn myDataColumn;

            myDataColumn = new DataColumn();
            myDataColumn.DataType = Type.GetType("System.String");
            myDataColumn.ColumnName = "Title";

            myDataColumn = new DataColumn();
            myDataColumn.DataType = Type.GetType("System.DateTime");
            myDataColumn.ColumnName = "StartTime";

            myDataColumn = new DataColumn();
            myDataColumn.DataType = Type.GetType("System.DateTime");
            myDataColumn.ColumnName = "EndTime";

            DataRow myRow;
            myRow = ReservedTime.NewRow();

            myRow["Title"] = "Matt";
            myRow["StartTime"] = DateTime.Parse("10/23/2010 10:15:00 AM");
            myRow["EndTime"] = DateTime.Parse("10/23/2010 10:30:00 AM");


            myRow = ReservedTime.NewRow();

            myRow["Title"] = "Lunch";
            myRow["StartTime"] = DateTime.Parse("10/23/2010 12:00:00 PM");
            myRow["EndTime"] = DateTime.Parse("10/23/2010 1:00:00 PM");


            myRow = ReservedTime.NewRow();

            myRow["Title"] = "Doris";
            myRow["StartTime"] = DateTime.Parse("10/23/2010 3:00:00 PM");
            myRow["EndTime"] = DateTime.Parse("10/23/2010 3:15:00 PM");


            return ReservedTime;


In a method I am trying to develop, I have two DataTables named slotCounts and createdSlots.  The table createdSlots has a column TIMESLOT which is typeof(DateTime), and slotCounts has a column SCHDTIME which is typeof(DateTime), from which these tables can be related.

I want create a new DataTable consisting of rows from createdSlots except where there is a matching row in slotCounts.  Conversely, I could just delete those rows from createdSlots that have a matching row in slotCounts.

I have been able to filter DataTables in some other methods, but not filtering one DataTable against another.  Is this possible?


I'm copying one datatable into another like this:  

temporaryTreeData = treeViewData.Copy();

How could I figure out how much time it takes for the copy to complete?




I'm taking the contents of an Excel Worksheet and placing it into a Datatable. One of the steps needed is to replace a country name with its 2 char abbreviation. I have a text file with the CountryName and the abbreviation that I load into a 2nd datatable. I had thought about using a datarelation but there will be times that there won't be an exact match but I need to keep the original country name for steps later in the project.

There isn't any updating databases or viewing the data. This is a conversion application that takes an Excel worksheet and outputs a text file with very specific requirements, ie order, column widths, delimters, etc.

And that's where I'm stuck. I can't figure out how do I replace the country name efficiently. What I need is something that will simulate what an SQL Update query does.

Although I'm using datasets I am open to using anything that will work.





I am not able to find any performance information on using SELECT for DataTable Vs LINQ to DataTable.

Does anyone know if its better to use LINQ to DataTable or the DataTable.SELECT when finding matching rows?



Hi All,

I have derived the DataTable and added a property to it. I need that property to work with ReadXml and WriteXml. Here WriteXml works fine. But when I do ReadXml, the property is not filling up. Any idea how to fix it?



  publicclass DataTableEx : DataTable
    public DataTableEx()
      : base()

    public DataTableEx(string tableName)
      : base(tableName)

    public DataTableEx(string tableName, string tableNamespace)
      : base(tableName, tableNamespace)

    public DataTableEx(SerializationInfo info, StreamingContext context)
      : base(info, context)
    [XmlElement(ElementName = "TotalRecordCount", Form = XmlSchemaForm.Qualified)]
    publicint TotalRecordCount

The Xml generated by WriteXml is:

<?xmlversion="1.0"standalone="yes"?><NewDataSet><xs:schemaid="NewDataSet"xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema"xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"><xs:elementname="NewDataSet"msdata:IsDataSet="true"msdata:MainDataTable="Test"msdata:UseCurrentLocale="true"><xs:complexType><xs:choiceminOccurs="0"maxOccurs="unbounded"><xs:elementname="Test"msdata:TotalRecordCount="20"><xs:complexType></xs:complexType></xs:element></xs:choice></xs:complexType></xs:element></xs:schema></NewDataSet>
In the above Xml RecordTotalCount is also streamed as Xml as per my requirement. But when I do ReadXml of the same xml, RecordTotalCount is not filling up.


Thanks in advance



I have a DataTable connected to a grid and I want to periodically replace all the rows in the table.
The easiest way for me to do this is to just switch the data source of the grid to the new table.
The problem is that this causes the grid to do some special stuff like loosing selected rows and reinitializing layout etc.
One solution would be to have a virtual DataTable that's connected to the grid at all times and change the underlying source of the virtual one.
What would be the easiest way to achieve this and still have the virtual one as a DataTable with DataRelations?


<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure