DataGridView changing time field to date?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi all,

I am loading a .csv file and just showing it in a datagrid. However, in the .csv field I have time like 00:30:00 but when is showed in the datagrid 12/30/1899 12:30 AM.

This is how I am reading it:

C#

OleDbConnection ExclConn = new OleDbConnection();
                // HDR = YES get header; Extended Properties = text parsing a text file
                ExclConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                                            "Data Source=" + dir + ";" +
                                            "Extended Properties=\"text;HDR=Yes;FMT=Delimited\"";
                //Open the Excel File
                string Exceldata = "SELECT * FROM [" + FileName + "]";
                OleDbDataAdapter dtadpt = new OleDbDataAdapter(Exceldata, ExclConn);
                //Create table
                dtadpt.Fill(dt);

I want to be able to just keep that format to save it into the sql database (which I am using there time(0) as the datatype.

Any suggestion?

Posted 22-Mar-11 11:39am

jalmonte

Updated 22-Mar-11 14:09pm

Henry Minute

v3

Add a Solution

2 solutions

Solution 1

Hi jalmonte,

It is being read as an integer value, thus the leading zeros mean nothing.

If you don't convert the value between the CSV and the datagrid, it has no way of knowing it is a time.

You need to specify in some fashion that that cell contains a date/time value, how you do that just depends on how exactly you are displaying the CSV in the datagrid.

Regards,
Frank

Posted 22-Mar-11 12:29pm

fcronin

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Henry Minute · Accepted Answer · 2011-03-22T14:15:00

Solution 2

Take a look at the MSDN page for the CellFormatting event handler.[^].

The example on that page shows how to format dates/times. Just alter it to suit your input and desired output.

Posted 22-Mar-11 14:15pm

Henry Minute

Comments

jalmonte 22-Mar-11 20:26pm

Thanks for the reply. I dont desire to change it in the datagrid, instead in the data table to be able to use the data table to bulkcopy into the sql. To modify it on the datagrid is: this.dataGridView1.Columns["MyColumnName"].DefaultCellStyle.Format = "hh:mm"; but how to modify it on the data table?

Henry Minute 22-Mar-11 20:39pm

OK.

You haven't shown how you are saving the data to the database, so this is only a suggestion. If there is the possibility to do it via a stored procedure or similar you could use the SQL Convert() function. Definition -> http://www.w3schools.com/sql/func_convert.asp. Examples ->http://www.sqlusa.com/bestpractices/datetimeconversion/.

You will need sunglasses to handle the color scheme on that last one.

Anyway, good luck. :)

jalmonte 22-Mar-11 20:59pm

Hi,
I never did that before, how do you will convert it if the data is outside sql server?

Henry Minute 22-Mar-11 21:38pm

I'm a little confused now.

If you aren't going to save this data to a Database why not use the CellFormatting() methodology?

I have never tried changing the column type in a DataTable, so I don't know if it is even possible.

Time for Plan 'C' have a look at this article from Here on CP http://www.codeproject.com/KB/database/filehelpers.aspx. In particular, look at the second code block it shows using a [FieldConverter(ConverterKind.Date, "ddMMyyyy")] attribute to modify data whilst reading it from a csv file. The library included in the article can read and write to many different formats so you may be able to use the attribute above AND read into a DataTable at the same time. No promises though, you'll have to study the article, visit their web site maybe to get an answer.

Sorry not to be more help.