2

Below is my code. I get my dates from two DatePickers in a Windows Form. I am trying to get the rows in Excel that are in between those two dates and do a SqlBulkCopy to SQL Server. I am getting the wrong dates as a result.

I have tried using a TextBox instead of DatePicker but that doesn't work either.

using (SqlConnection strConnection = new SqlConnection(Connection))
{
    using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
    {
        var buttons = (new[] { groupBox1 }
            .SelectMany(g => g.Controls.OfType<RadioButton>()
            .Where(r => r.Checked)))
            .ToList();

        SqlCommand query = new SqlCommand();

        if (buttons[0].Text == "Date Range")
        {
            query.CommandText = "Select * from [Sheet0$]  where [ChangedDate] between @date1 and @date2;";
        }
        else
        {
            query.CommandText = "Select * from [Sheet0$]";
        }

        //Create OleDbCommand to fetch data from Excel
        using (OleDbCommand cmd = new OleDbCommand(query.CommandText, excelConnection))
        {
            if (buttons[0].Text == "Date Range")
            {
                string fromDate = this.fromDate.Value.Date.ToString("MM/dd/yyyy");
                string toDate = this.toDate.Value.Date.ToString("MM/dd/yyyy");                                   
                cmd.Parameters.AddWithValue("@date1", fromDate);
                cmd.Parameters.AddWithValue("@date2", toDate);
            }                               

            excelConnection.Open();
            using (OleDbDataReader dReader = cmd.ExecuteReader())
            {                
                using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection, SqlBulkCopyOptions.TableLock |
                    SqlBulkCopyOptions.FireTriggers |
                    SqlBulkCopyOptions.UseInternalTransaction,
                    null))
                {
                    e.Result = 0;
                    sqlBulk.DestinationTableName = "tblCMHC";

                    while (dReader.Read())
                    {                        
                        sqlBulk.WriteToServer(dReader);
                    }
                }
            }
        }
    }
}
Lews Therin
  • 3,707
  • 2
  • 27
  • 53
Srivaishnav Gandhe
  • 139
  • 8
  • 1
    Dates in Excel are, well, special. They are floating point numbers with the integer part representing the day (as days since the start of the Excel epoch) and the fractional part, the time of day (for example, if the fractional part is 0.5, then it represents noon). Have you checked if the SQL interface to Excel handles dates in a SQL-ish way or in an Excel-ish way? – Flydog57 Mar 19 '19 at 19:58
  • If you want to see the floating point value of a date in Excel, change the format of the cell to a numeric format (from a date format). For example, I'm in the Central North American time zone (Central Daylight Saving Time) and right now (15:03, on 19-Mar-2019) shows up as 43543.6309974537 – Flydog57 Mar 19 '19 at 20:09
  • 1
    Both `Date.ToString()` and `AddWithValue` are always risky in databases, try `cmd.Parameters.Add("@date1", OleDbType.DateTime).Value = this.fromDate.Value`. – Dour High Arch Mar 19 '19 at 20:11
  • 1
    DateTime in c# has a method called `ToOADate()` which is used to convert c# dates to Excel understandable format. You can give it a try. Check it [here](https://learn.microsoft.com/en-us/dotnet/api/system.datetime.tooadate?view=netframework-4.7.2) – prinkpan Mar 19 '19 at 20:12
  • Tried both ToOADate() and cmd.Parameters.Add("@date1", OleDbType.DateTime).Value = this.fromDate.Value. Yet, not working! – Srivaishnav Gandhe Mar 20 '19 at 14:34

1 Answers1

2

I found the answer. Problem was not with my code. In fact I was getting the right search results from excel but I needed to change the format of date column in excel to be able to insert records into SQL server database in the acceptable format.

Srivaishnav Gandhe
  • 139
  • 8