CODEWITHSUNDEEP
c#mysql
prkash
prkash

Reputation: 449

How to extract month name of date from MySql query

I have this query which checks for a data repeating four times on the same month 4 or more times. I need to extract the month name or number from this and pass it to string or int.

MySqlConnection connect = new MySqlConnection(MyConString);
string query = "SELECT agentlogin, COUNT(agentlogin), LAST_DAY(`date`) AS Month FROM pending WHERE agentlogin = @login GROUP BY LAST_DAY(`date`) HAVING COUNT(agentlogin) >= 4";
MySqlCommand comm = new MySqlCommand(query, connect);
comm.Parameters.AddWithValue("@login", Label1.Text);
connect.Open();
MySqlDataReader rdr = comm.ExecuteReader();
while (rdr.Read())
{
    lblmsg.Text = "GrpM Alert!";
    string getMonth = ?;
}

So how can I get this done?

Thanks in advance.

Upvotes: 1

Views: 280

Answers (3)

Michał Turczyn
Michał Turczyn

Reputation: 37367

The easiest would be:

string getMonth = rdr["Month"].ToString();

And replace LAST_DAY(date) with MONTH(LAST_DAY(date)) or some variation like MONTH(date).

At every step of reading the query with rdr.Read() you can access current row fields through MySqlDataReader object using it just like array/dictionary: rdr[int i], where i would be index of a field in a row or rdr[string fieldName], where fieldName is name of a column, which you want to get from particular row.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

I think you should be grouping by the month, year, and agent login, then selecting only the year and month. We can use DATE_FORMAT in this case:

SELECT
    DATE_FORMAT(date, '%Y-%m') AS yearmonth
FROM pending
WHERE
    agentlogin = @login
GROUP BY
    agentlogin,     -- optional, but included for correctness
    DATE_FORMAT(date, '%Y-%m')
HAVING
    COUNT(*) >= 4;

Note that this may return more than one month value, if a login happens to match more than one month.

while (rdr.Read())
{
    lblmsg.Text = "GrpM Alert!";
    int monthNumber = rdr.GetString("yearmonth");
}

The reason for wanting to group by the year and month is that different years could have the same month, and in this case, you probably don't want to report them as being the same thing.

Upvotes: 0

fubo
fubo

Reputation: 45947

Read the Month field and format it to its name

while (rdr.Read())
{
    DateTime date = rdr.GetDateTime("Month"); 
    string getMonth = date.ToString("MMM");
}

Upvotes: 2

Related Questions