How to get only the year and month part of a date to compare?

Question

In my application I want to count the number of records with the same current year and month. So my logic is to compare the current Year and Month to the date_needed column in my table. Here's how I did it:

using (MySqlConnection con = new MySqlConnection(serverstring))
{
         con.Open();
         string query = "SELECT * FROM tblOrder WHERE date_needed=@dateTimeNow";
         using (MySqlCommand cmd = new MySqlCommand(query, con))
         {
               cmd.Parameters.AddWithValue("@dateTimeNow", DateTime.Now.ToString("yyyy-MM"));             using (MySqlDataReader dr = cmd.ExecuteReader())
               {
                      int count = 0;
                      while (dr.Read())
                      {
                          count++;
                      }
                      MessageBox.Show(count.ToString());
                }
         }
}

I know that it doesn't work because in my messagebox it shows zero instead of one record. What do you think is the problem?

Is `date_needed` really a varchar field storing date in `yyyy-MM` format ? — V4Vendetta, May 16 '13 at 06:02
@RajeevKumar, I want to compare the current time in mySQL database table `tblOrder` and with the column `date_needed`, so I have this query `string query = "SELECT * FROM tblOrder WHERE date_needed=@dateTimeNow";` — Harvey, May 16 '13 at 06:03
I guess then it won't work you will have to format the LHS `date_needed` to the format you are passing like [DATE_FORMAT(fieldname,"%Y-%m")](http://stackoverflow.com/a/5091194/570150) — V4Vendetta, May 16 '13 at 06:06
_Just a hint_: I guess you calculate with count variable just how many rows gets your query. Instead of that, you can use `ExecuteNonQuery` method which does exactly the same.. — Soner Gönül, May 16 '13 at 06:08

score 5 · Accepted Answer · answered May 16 '13 at 06:06

You should supply a complete date (as a DateTime, without formatting it - text conversions are almost always a bad idea when sending parameter values to a database) and use the MySQL date/time functions to compare the values.

For example:

string query = @"SELECT * FROM tblOrder 
                 WHERE MONTH(date_needed) = MONTH(@dateTimeNow)
                   AND YEAR(date_needed) = YEAR(@dateTimeNow)";

Alternatively, you could pass the month and year as separate parameters:

string query = @"SELECT * FROM tblOrder 
                 WHERE MONTH(date_needed) = @month
                   AND YEAR(date_needed) = @year";

Or - possibly more performantly - you could give start and end points:

string query = @"SELECT * FROM tblOrder 
                 WHERE date_needed >= @start AND date_needed < @end";

Here you'd set @start to the start of this month, and @end to the start of the next month. You could work those out as:

// Consider using UtcNow.Date instead. Basically, think about time zones.
DateTime today = DateTime.Today;
DateTime start = new DateTime(today.Year, today.Month, 1);
DateTime end = start.AddMonths(1);

score 0 · Answer 2 · answered May 16 '13 at 06:07

0

If you wanna get Count of rows in sql use

But Need to pass Complete date value in @dateTimeNow

SELECT Count(*) FROM tblOrder WHERE MONTH(date_column)= MONTH(@dateTimeNow) and YEAR(date_column) = YEAR(@dateTimeNow)

answered May 16 '13 at 06:07

Rajeev Kumar

4,901
8
48
83

score 0 · Answer 3 · answered May 16 '13 at 06:08

If you want to do it all on the MySQL end, try this:

SELECT *
FROM tblOrder
WHERE EXTRACT(YEAR_MONTH FROM date_needed) = EXTRACT(YEAR_MONTH FROM CURRENT_DATE)

To make it fit your @dateTimeNow parameter, which is formatted as yyyy-MM (in .NET this means the year with century, followed by a dash, followed by the month as two digits), do this:

SELECT *
FROM tblOrder
WHERE @dateTimeNow = DATE_FORMAT(date_needed, '%Y-%m')

How to get only the year and month part of a date to compare?

3 Answers3