Click here to Skip to main content
14,932,329 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am importing an excel file into SqLite database, the results are 2/28/2021 0:00.
I only want the date. When I Manually enter the Datetime picker it is correct.

C#
<pre>private void btn_Import_Click(object sender, EventArgs e)
        {
            OleDbConnection theConnection = new OleDbConnection(@"provider=Microsoft.JET.OLEDB.4.0;data source='" + txtFileName.Text + "';Extended Properties=\"Excel 8.0;HRD=NO;IMEX=1\"");

            theConnection.Open();
            OleDbDataAdapter theDataAdapter = new OleDbDataAdapter("Select * from[Sheet1$]", theConnection);
            DataSet theSD = new DataSet();
            DataTable dt = new DataTable();
            theDataAdapter.Fill(dt);
            this.dataGridView1.DataSource = dt.DefaultView;
        }

        void fillGrid()
        {
            con.Open();
            SQLiteDataAdapter da = new SQLiteDataAdapter("Select * from Information order by ID", con);
            DataTable dt = new DataTable();
            da.Fill(dt);
            dataGridView1.DataSource = dt;
            
         //   dataGridView1.Columns[1].DefaultCellStyle.Format = "dd/MM/yyyy";
            con.Close();
        }

        private void btn_Save_Click(object sender, EventArgs e)
        {

            con.Open();
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                
                //if (LibID.Text == "*")
                //{
                SQLiteCommand cmd = new SQLiteCommand("insert into Information(Production_Date,Part_Number,Cart,Qty,Location,User)" +
                    " values('" + dataGridView1.Rows[i].Cells[1].Value + "','" + dataGridView1.Rows[i].Cells[2].Value + "'," +
                    "'" + dataGridView1.Rows[i].Cells[3].Value + "','" + dataGridView1.Rows[i].Cells[4].Value + "'," +
                    "'" + dataGridView1.Rows[i].Cells[5].Value + "','" + dataGridView1.Rows[i].Cells[6].Value + "')", con);
                
                cmd.ExecuteNonQuery();
            }
            con.Close();
                       MessageBox.Show("Successful Saved");
                       fillGrid();
                       txtFileName.Text = string.Empty; // Clear TextBox


        }
            }
        }


What I have tried:

Changing Sqlite from text to real, int and
dataGridView1.Columns[1].DefaultCellStyle.Format = "dd/MM/yyyy";
Posted
Updated 28-Feb-21 6:41am

A .NET DateTime value is just that: a date and a time, you can't have the one without the other.

However, when you display the value, you can select what you want to display:
C#
myDataGridView.Columns[MyDateTimeColumn].DefaultCellStyle.Format = "dd/MM/yyyy";
If that isn't working for you, then I'd suggest that it's your "magic number" that's causing it - .NET use a zero based offset, and most spreadsheets have the DateTime value in the first column.
If that doesn't fix it, then use the debugger to see exactly what you get from your spreadsheet - if the column is passed as a string instead of a DateTime then no amount of formatting will change it as the DGV will display it as the original string all the time.
   
Comments
BillWoodruff 1-Mar-21 1:55am
   
+5
Member 12349103 4-Mar-21 17:35pm
   
This is what worked.
OleDbDataAdapter theDataAdapter =
new OleDbDataAdapter("SELECT [ID], FORMAT([Production_Date], 'M/d/yyyy') as [Production_Date],[Part_Number],[Cart],[Qty],[Process],[Model],[User] FROM [Sheet1$]", theConnection);
Quote:
I am importing an excel file into SqLite database, the results are 2/28/2021 0:00.


When you import data from Excel to SQLite, you have to properly "format" date... SQLite is able to convert date/time when date/time column is using ISO date format.

Take a look here: SQLite 3.27 | db<>fiddle[^]
select date('2021-02-28 18:35:00'), time('2021-02-28 18:35:00')
UNION ALL
select date('28-02-2021 18:35:00'), time('28-02-2021 18:35:00')

returns:
(date) 		(time)
2021-02-28 	18:35:00
null		null
   
v2
Comments
BillWoodruff 1-Mar-21 1:55am
   
+5
Maciej Los 1-Mar-21 2:11am
   
Thank you, Bill.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900