C# winform remove time from datatime

Question

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.

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  
  
  
        }  
            }  
        }  


  

Answer 1

Try one of solutions:

private void btn_Import_Click(object sender, EventArgs e)
{
   . . .
   this.dataGridView1.DataSource = dt.DefaultView;
   dataGridView1.Columns["Production_Date"].DefaultCellStyle.Format = "d";
}

Answer 2

Hi

private void button1_Click(object sender, EventArgs e)
    {
      DateTime dt;

      string dt1;
       dt = DateTime.Now;
      string tdDay,tdMonth,tdYear;
      //Befor display date and time
      MessageBox.Show(dt.ToString());

      tdDay = (dt.Day).ToString();
      tdMonth = (dt.Month).ToString();
      tdYear = (dt.Year).ToString();
      dt1 = tdDay + "/" + tdMonth + "/" + tdYear;
      //After display date only
       MessageBox.Show(dt1);

    }

Answer 3

If you don't ever need the time part, modifying your SQLLite select statement to use "substr(Production_Date,0,10) as Production_Date" will do.

It will be better if you can eliminate the time part when you read from Excel file, though.

Also note that since for SQLite, the n of varchar(n) is ignored and whatever you stored is never truncated based on it, the other possible strategy to declare the field varchar(10) and hope it will silently truncate the excess characters will fail.

Answer 4

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);