How to remove time from datetime column in dataset?

Question

I have to export data to excel sheet in asp.net c# application. Now I have wriiten below lines of code

 DataTable dt = new DataTable();
        dt = ds.Tables[0];
        foreach (DataRow row in dt.Rows)
        {
            row["ExpiryDate"] = Convert.ToDateTime(row["ExpiryDate"]).ToShortDateString();
        }
        DataSet dsn = new DataSet();
        DataTable dtcopy = dt.Clone();
        dsn.Tables.Add(dtcopy);
WebUtility.GenrateExcel(ds, "ExpiredDocuments");

I want to first remove time from data set's Expiry date column and then pass it to the generate Excel function so that time will not appear in Expiry date column in excel... Please help the above code is not working...

Let me ask. Is _ExpiryDate_ a DateTime column or a string one? — Steve, Jan 20 '16 at 10:07
What is the format of `ds.Tables[0].Rows[i]["ExpiryDate"]`? Any error code? — Ian, Jan 20 '16 at 10:11
In this case whatever you do it'll display the time since `DateTime` is the datatype of your `ExpiryDate` column. Better add a string column and convert the `ExpiryDate ` value to date only and assign it there. — Irshad, Jan 20 '16 at 10:21
Your line inside the for loop doesn't do anything. Sure you call convert on a column and then ask to get a string in the ShortDate format but you don't reassign this value to anything. When you call your GenerateExcel method the ExpiryDate is still a DateTime. And no you can't assign the string back to the column because it is still a datetime column. This conversion should be executed by the GenerateExcel method (perhaps passing a flag to instruct the removal of time values from datetime columns) — Steve, Jan 20 '16 at 10:21
try `ds.Tables[0].Rows[i]["ExpiryDate"]=Convert.ToDateTime(ds.Tables[0].Rows[i]["ExpiryDate"]).ToShortDateString();` to update the value of the dataset. other wise you will need to use [this link](http://stackoverflow.com/questions/7750603/exporting-to-excel-loses-the-date-format) — शेखर, Jan 20 '16 at 10:23
can we pass old dataset values in new dataset after removing time... if yes — Nida, Jan 20 '16 at 10:23
You can use [linq to datatable](https://msdn.microsoft.com/en-us/library/bb552415%28v=vs.110%29.aspx) for this purpose. [More example](https://msdn.microsoft.com/en-us/library/bb399401%28v=vs.110%29.aspx) — शेखर, Jan 20 '16 at 10:28
If you want to display the value in *Excel* in a certain way, post the code that generates *the Excel file*. At most, you need to change the sheet column's format. You can also get the date part of a DateTime with DateTime.Date, eg `row["ExpiryDate"]=((DateTime)row["ExpiryDate"]).Date;` — Panagiotis Kanavos, Jan 20 '16 at 11:08

score -1 · Answer 1 · answered Jan 20 '16 at 10:09

-1

I guess, you need look through this: https://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx

You should use smth like

Convert.ToDateTime(ds.Tables[0].Rows[i]["ExpiryDate"]).ToString("MM/dd/yyyy")

Or whatever you need.

answered Jan 20 '16 at 10:09

Ivan Rubanau

374
2
12

1

This doesn't CHANGE (hint) anything – Steve Jan 20 '16 at 10:11
This *could* change the data and break it beyond recognition - in any locale that doesn't use the US date format – Panagiotis Kanavos Jan 20 '16 at 11:05

score -1 · Answer 2 · answered Jan 20 '16 at 11:03

These are the steps you need to do in order to accomplish what you are asking:

Create a new column of type string
Set the new column's value to ShortDateString of the corresponding "ExpiryDate" column
Remove the "ExpiryDate" column
Move the new string column to the position of the old "ExpiryDate" column
Rename the new column to "ExpiryDate"

Like so:

        dt.Columns.Add("ExpiryDateString", typeof(String));
        foreach(DataRow row in dt.Rows)
        {
            row["ExpiryDateString"] = ((DateTime)row["ExpiryDate"]).ToShortDateString();
        }
        int columnNumber = dt.Columns["ExpiryDate"].Ordinal;
        dt.Columns.Remove("ExpiryDate");
        dt.Columns["ExpiryDateString"].SetOrdinal(columnNumber);
        dt.Columns["ExpiryDateString"].ColumnName = "ExpiryDate";

How to remove time from datetime column in dataset?

2 Answers2