3
Answers

How to change the datetime format of a column in a dataset

Ask a question
Sri

Sri

14y
11.1k
1

Hi
I have two excel files having some 10 columns each with some date fields. I have a task to merge these two excel files and compare the dates. I am able to load the excel files in the datasets but the format of the date columns are defaulted to mm/dd/yyyy hh:mm:ss. I am not sure how can I store the excel data with date as mm/dd/yyyy.
Following is the code that I use to populate the dataset with an excel file. Is there any way to format the date column when I store it into a dataset or modify an existing dataset with formatting of date columns.
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.xls;Extended Properties=Excel 8.0;";
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
DbDataAdapter adapter = factory.CreateDataAdapter();
DbCommand selectCommand = factory.CreateCommand();
selectCommand.CommandText = "SELECT Last_Name,First_Name,Completion_Date,Completion_Date2,Completion_Date3,Completion_Date4 FROM " + table + " WHERE Last_Name IS NOT NULL ORDER BY Last_Name, First_Name";

DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
selectCommand.Connection = connection;
adapter.SelectCommand = selectCommand;
DataSet ds = new DataSet();
adapter.Fill(ds);
return ds;

You help is much appreciated!
Regards

Answers (3)