question

Booney-3048 avatar image
0 Votes"
Booney-3048 asked ·

C# winform remove time from datatime

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.

72628-screenshot-2021-02-28-101908.png



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


dotnet-csharpwindows-formsdotnet-sqlite
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Booney-3048,
>>I am importing an excel file into SqLite database, the results are 2/28/2021 0:00
Based on your code, I saw that you import excle data to datagridview and then insert the data of the datagriview into the database.
So what is the data type of "Production_Date" in the database?
And I suggest you can use DateTime.ParseExact method to get the date from your datatime columns, then insert them.

 string myDate = dataGridView1.Rows[i].Cells[1].Value.ToString();// myDate = "2/28/2021 07:50:00 AM";
 DateTime dt1 = DateTime.ParseExact(myDate, "MM/dd/yyyy hh:mm:ss tt", CultureInfo.InvariantCulture);
 DateTime dt2 = dt1.Date;
 cmd.CommandText = "insert into test(Id,time)" + " values('" + dataGridView1.Rows[i].Cells[0].Value+ "','" + dt2 + "')";

Best Regards,
Daniel Zhang

0 Votes 0 ·
Viorel-1 avatar image
1 Vote"
Viorel-1 answered ·

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

· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Getting error System.NullReferenceException: 'Object reference not set to an instance of an object.'

0 Votes 0 ·

Well use the Visual Stuido Debugger Quickwatch and find out what is a null valued object. If the object is null, it doesn't exist.

0 Votes 0 ·
AbdulhakimMElrhumi-5836 avatar image
0 Votes"
AbdulhakimMElrhumi-5836 answered ·

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



· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@AbdulhakimMElrhumi-5836 I wonder when can I begin to see you really trying to answer a question, and I am still waiting.

1 Vote 1 ·

AbdulhakimMElrhumi-5836


I don't want to show the time.

0 Votes 0 ·
cheong00 avatar image
0 Votes"
cheong00 answered ·

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.


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Booney-3048 avatar image
0 Votes"
Booney-3048 answered ·

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);
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.