Issue getting weekly birthdays from database at end of the month

Question

I have a program that shows the weekly birthdays of members every week. This code is working perfectly, until the end of the month. Whenever we reach the end of the month and there are less than 7 days left in the current month, the code no longer lists the birthdays.

I know this is because the week is now spanning across two months, but I have tried everything and just can't figure out what I must do in order to fix this. This is probably a simple issue. Any help would be greatly appreciated. Here is my code:

public void CheckDayOfWeekAndReturnBirthdaysInDateRange()
    {
        //Check the current day of the week and make calculations according to find out which week to use.
        if (DateTime.Now.DayOfWeek.ToString() == "Sunday")
        {
            DateTime weekStart = DateTime.Now.Date;
            DateTime weekEnd = DateTime.Now.AddDays(7);

            Birthdays = eventsService.ReturnBirthdaysForCurrentWeek(weekStart, weekEnd);
        }
        else if (DateTime.Now.DayOfWeek.ToString() == "Monday")
        {
            DateTime weekStart = DateTime.Now.AddDays(-1);
            DateTime weekEnd = DateTime.Now.AddDays(6);

            Birthdays = eventsService.ReturnBirthdaysForCurrentWeek(weekStart, weekEnd);
        }
        else if (DateTime.Now.DayOfWeek.ToString() == "Tuesday")
        {
            DateTime weekStart = DateTime.Now.AddDays(-2);
            DateTime weekEnd = DateTime.Now.AddDays(5);

            Birthdays = eventsService.ReturnBirthdaysForCurrentWeek(weekStart, weekEnd);
        }
        else if (DateTime.Now.DayOfWeek.ToString() == "Wednesday")
        {
            DateTime weekStart = DateTime.Now.AddDays(-3);
            DateTime weekEnd = DateTime.Now.AddDays(4);

            Birthdays = eventsService.ReturnBirthdaysForCurrentWeek(weekStart, weekEnd);
        }
        else if (DateTime.Now.DayOfWeek.ToString() == "Thursday")
        {
            DateTime weekStart = DateTime.Now.AddDays(-4);
            DateTime weekEnd = DateTime.Now.AddDays(3);

            Birthdays = eventsService.ReturnBirthdaysForCurrentWeek(weekStart, weekEnd);
        }
        else if (DateTime.Now.DayOfWeek.ToString() == "Friday")
        {
            DateTime weekStart = DateTime.Now.AddDays(-5);
            DateTime weekEnd = DateTime.Now.AddDays(2);

            Birthdays = eventsService.ReturnBirthdaysForCurrentWeek(weekStart, weekEnd);
        }
        else if (DateTime.Now.DayOfWeek.ToString() == "Saturday")
        {
            DateTime weekStart = DateTime.Now.AddDays(-6);
            DateTime weekEnd = DateTime.Now.AddDays(1);

            Birthdays = eventsService.ReturnBirthdaysForCurrentWeek(weekStart, weekEnd);
        }
    }

    public void LoadDatagridComponents(DataGridView dataBirthdays)
    {
        dataBirthdays.AutoGenerateColumns = false;
        dataBirthdays.Columns[0].Visible = true;
        dataBirthdays.Columns[1].Visible = true;
        dataBirthdays.Columns[2].Visible = true;

        dataBirthdays.Columns[0].HeaderText = "Name";
        dataBirthdays.Columns[1].HeaderText = "Surname";
        dataBirthdays.Columns[2].HeaderText = "Birthday";

        //Loop through each birthday member and change date of birth to month/day fromat.
        int currentRow = 0;
        foreach (DataGridViewRow row in dataBirthdays.Rows)
        {
            string birthday = Convert.ToDateTime(dataBirthdays.Rows[currentRow].Cells[2].Value).ToString("MMMM dd");
            dataBirthdays.Rows[currentRow].Cells[2].Value = birthday;
            currentRow++;
        }
    }

Then from there it goes here:

public List<Birthdays> ReturnBirthdaysForCurrentWeek(DateTime weekStart, DateTime weekEnd)
    {
        var birthdays = new List<Birthdays>();

        int month = DateTime.Now.Month;
        int dayStartOfWeek = weekStart.Day;
        int dayEndOfWeek = weekEnd.Day;

        using (MySqlConnection Conn = new MySqlConnection(Connect.sConnStr))
        {
            Conn.Open();
            string sSql = "SELECT name, surname, date_birth FROM members WHERE MONTH(date_birth) = " + month + " AND DAY(date_birth) BETWEEN " + dayStartOfWeek + " AND " + dayEndOfWeek + ";";

            MySqlDataReader reader = Connect.getDataCommand(sSql, Conn).ExecuteReader();

            while (reader.Read())
            {
                var birthday = new Birthdays()
                {
                    MemberName = reader["name"].ToString(),
                    MemberSurname = reader["surname"].ToString(),
                    MemberBirthday = reader["date_birth"].ToString()
                };
                birthdays.Add(birthday);
            }

            Conn.Close();
            Conn.Dispose();
        }
        return birthdays;
    }

I'm sure you'll get several comments on comparing days of the week to a string, separation of concerns, etc so I'll skip all that. It would probably be helpful if you included some sample data as well as what your method is returning (i.e. demonstrate exactly how it's not working) — Jedidja, Dec 07 '14 at 17:21
Probably, you can do it on database level. Check out this question: https://stackoverflow.com/questions/3769555/what-is-the-nicest-way-to-select-the-current-week-in-mysql — default locale, Dec 07 '14 at 17:34
Check this answer: http://stackoverflow.com/a/15433121/897326. More on datepart: http://msdn.microsoft.com/en-us/library/ms174420.aspx — Victor Zakharov, Dec 07 '14 at 21:51

score 1 · Accepted Answer · answered Dec 07 '14 at 20:48

Rather than breaking up the date comparison logic into month and day comparisons (which has the bug you've discovered), why not compare the dates directly?

For example, this can be done via sql parameters:

string sSql = "SELECT name, surname, date_birth FROM members WHERE date_birth BETWEEN @param_val_1 AND @param_val_2";
MySqlCommand command = Connect.getDataCommand(sSql, Conn);
command.Parameters.AddWithValue("@param_val_1", weekStart);
command.Parameters.AddWithValue("@param_val_2", weekEnd);

You can also simplify the code to get the "current" week to avoid all the case logic:

var today = DateTime.Today;
// start with the numbers [0 .. 7)
var startOfWeek = Enumerable.Range(0, count: 7)
    // for each, subtract that many days from today
    .Select(i => today.AddDays(-i))
    // find the first such date that is a Sunday
    .First(dt => dt.DayOfWeek == DayOfWeek.Sunday);
var endOfWeek = startOfWeek.AddDays(6);

Depending on how you store your data, you may have to be careful with the time component as well. DateTime.Today gives you the current day with no time component (e. g. midnight on that day). However, if the dates in your database have other time components you may have to adjust the logic to account for those.

Issue getting weekly birthdays from database at end of the month

1 Answers1