How can I get a list of students with a birthday in the current month?

Question

I wrote the code, but I can get all the dates. How to get a date in the current month?

String connectionString = @"data source = localhost; initial catalog = StudentsBirthday; integrated security = SSPI;";
SqlConnection con = new SqlConnection(connectionString);
List<DateTime> birthdays = new List<DateTime>();

using (con)
{
    con.Open();
    SqlCommand cmd = new SqlCommand("Select Birthday From Students",con);
    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        birthdays.Add(reader.GetDateTime(0));
    }
}

foreach (DateTime Birthday in birthdays)
{
    Console.WriteLine(Birthday);
}

Console.ReadKey();

haldo · Accepted Answer · 2019-09-29T19:11:02.103

2

You could use this SQL query:

SELECT Birthday FROM Students 
WHERE MONTH(Birthday) = MONTH(GETDATE())  
ORDER BY Birthday

This compares the month of the birthday and the current month from the GETDATE() function. This will order the birthdays by (ascending) date.

You can add this to your code like so:

var sqlQuery = "SELECT Birthday FROM Students WHERE MONTH(Birthday) = MONTH(GETDATE()) ORDER BY Birthday"
SqlCommand cmd = new SqlCommand(sqlQuery,con);

edited Sep 29 '19 at 19:11

answered Sep 29 '19 at 18:51

haldo

14,512
5
46
52

This gets all birthdates in the current month, not considering the year. – gsharp Sep 29 '19 at 18:55
@gsharp I guess it depends what the OP wants. Why would we need to consider the year too? If they want to know which students have birthdays this month, does it matter which year they are born? – haldo Sep 29 '19 at 18:57
@haldo actually true :-) sorry for the downvote... really depends what the OP wants. Will upvote it as soon as SO allows me. – gsharp Sep 29 '19 at 19:01
1

@haldo actually if you want to have it from the current year. it wouldn't be a student i guess... :-p – gsharp Sep 29 '19 at 19:04
This is a [non-sargable query](https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable). It will force a table scan and not use an index. The more rows in your table, the slower it will be. – Matt Johnson-Pint Sep 29 '19 at 19:09
@MattJohnson-Pint I know... I was hoping no-one would point that out! Hopefully it's not a large database. Is there a sargable method? – haldo Sep 29 '19 at 19:11
Ha! I do tend to point things out. LOL! Yes, you can make a computed column for the month and build on index on that. Of course this assumes the database is read-heavy rather than write-heavy. It also assumes the table size is large enough for an index to matter. On a small table, a scan may be faster than the cost of building an index. – Matt Johnson-Pint Sep 30 '19 at 00:00

How can I get a list of students with a birthday in the current month?

1 Answers1