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

Sri

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kirtan Patel17y ago
Accepted answer
Try to Use format Funtion Like in below Query

SELECT Last_Name,First_Name,Completion_Date,format(Completion_Date2,'mm/dd/yyyy'),format(Completion_Date3,'mm/dd/yyyy'),format(Completion_Date4,'mm/dd/yyyy') FROM " + table + " WHERE Last_Name IS NOT NULL ORDER BY Last_Name, First_Name

------------

format(datecolumn , )
0
Kirtan Patel17y ago
Friend ,

Here are List Of All Function Used By Oledb Jet 4.0 all of them you cant use with excel but some will work.

http://www.techonthenet.com/access/functions/

Here is Excel Date Format VBA

http://www.techonthenet.com/excel/formulas/format_date.php
0
Sri17y ago
kirtan
excellent! i didn't like the answer but loved it! can you please guide me where can i find all the functions that we can use with these queries. I guess these queries doesn't take normal sql functions.
thanks again!
0