Select months between two dates

Question

I am trying to create a form which will take attendance data from a database and transfer to a excel sheet to print out.

I have created the form and with two datetimepickers I have managed to get the month to begin the travel claim and the month I want to claim too.

I am struggling with separating this into individual months so that I can use the individual months to intigrate the database.

so far I have

    if (TodateTimePicker.Value.Date <= FromdateTimePicker.Value.Date)
    {
        MessageBox.Show("End Date must be after Start Date");
    }
    else
    {
        fromDate = FromdateTimePicker.Value.Date;
        toDate = TodateTimePicker.Value.Date;

        fromDate = (fromDate.AddDays(1 - fromDate.Day));

        toDate = (toDate.AddDays(1 - toDate.Day)).AddMonths(1).AddDays(-1)
    }

I also have the database command I want to use

            com = new OleDbCommand("SELECT count(*), FROM Attendances WHERE year([Attendance_Date]) = ? GROUP BY [Person] ORDER BY count(*) DESC", Program.DB_CONNECTION);
            com.Parameters.Add(new OleDbParameter("", month));
            com.Parameters.Add(new OleDbParameter("", p.ID));

My real question is how to get the 1st of each month and the last date of each month between the two dates selected?

Answer 1

I came up with a solution where I calculated the number of months between the two dates, then ran through a loop to add the number of months to the start date to give me the first and last day of each month between the two original dates, probably not the neatest way but it works exactly as I planned.

int months = ((toDate.Year - fromDate.Year) * 12 + toDate.Month - fromDate.Month) + 1; // find the munber of months between the two dates and add one

        DateTime dateofclaim = new DateTime(); // define dateofclame to a new date  

        for (int i = 0; i < months; i++) // for each month run the code below, this will first run the fromDate then add a month until it gets to the toDate
        {
            dateofclaim = fromDate.AddMonths(i); // add one month to the start date

            claimfromDate = (dateofclaim.AddDays(1 - dateofclaim.Day)); // calculate the first date of the month

            claimtoDate = (dateofclaim.AddDays(1 - dateofclaim.Day)).AddMonths(1).AddDays(-1); // calculate the last day of the month

            OleDbCommand com = new OleDbCommand("SELECT sum([sum]) FROM Attendance WHERE ([Attendance_Date]) >= ? AND ([Attendance_Date]) <= ? AND [Person] = ?", Program.DB_CONNECTION); // count the total attendances in the month
            com.Parameters.Add(new OleDbParameter("", claimfromDate));
            com.Parameters.Add(new OleDbParameter("", claimtoDate));
            com.Parameters.Add(new OleDbParameter("", person.ID));

            OleDbDataReader dr = com.ExecuteReader(); // start reader  

            if (dr.Read()) // if a result is returned
            {
                try
                {
                    int attendance = Convert.ToInt32(dr.GetDouble(0)); // this is how many attendances have been counted for the selected month

                    ws.get_Range("B" + row.ToString()).Value2 = dateofclaim.ToString("MMM yyyy"); // print the Month of the clame into Colum B and row 83 then adding one for each following month
                    ws.get_Range("I" + row.ToString()).Value2 = attendance.ToString(); // print the number of attendances into Colum B and the respective row
                    row++; // add one to the row so next time this runs the data will be on the next row 
                }
                catch // if nothing as returned from the database then add the month and 0 in the attendance colum
                {
                    ws.get_Range("B" + row.ToString()).Value2 = dateofclaim.ToString("MMM yyyy");
                    ws.get_Range("I" + row.ToString()).Value2 = "0";
                    row++;
                }
            }
            dr.Close(); // close the database reader
        }