CODEWITHSUNDEEP
c#sql-server
Alternatex
Alternatex

Reputation: 1552

How to select month for datatype "date"?

I'm doing a program in C# where I can select and put data into a dataGridView by year and month. The year part is easy. I select the year from a comboBox and I do this query:

"SELECT * FROM table WHERE YEAR(date) = '" + year_comboBox.Text + "'";

Because the year format in the database is 2010, 2011, 2012 and so forth, it all works fine. But when it comes to months, they're in my comboBox as January, February, March etc. and their format in the database is 01, 02, 03, etc. So I would need a different type of query or I'd need to write more code so I can convert the January, February and the other month strings into 01, 02 and so forth and that's not really code-efficient.

So is there a simple type of query I can make to make this work without typing having to convert my month strings?

Upvotes: 0

Views: 1974

Answers (3)

Jay Riggs
Jay Riggs

Reputation: 53593

You can convert a month's name as a string into a DateTime using the DateTime.ParseExact method:

var monthString = DateTime.ParseExact("April", "MMMM", System.Globalization.CultureInfo.InvariantCulture).Month.ToString("00");

The code above will assign "04" to monthString.

Upvotes: 2

Habib
Habib

Reputation: 223257

string query = @"SELECT * FROM table WHERE MONTH(date) = '" +  DATEPART(mm,CAST(month_combobox+ ' 1900' AS DATETIME)) + "'";

With DatePart you could get the month name from the number. (Not sure about the ' or " )

Upvotes: 0

Adam V
Adam V

Reputation: 6356

I would think you can modify your combobox so that in addition to the text of January, February, March, etc., each record also has a value of 1, 2, 3, etc. You can then pass the value to SQL to use in your month query.

(I may be wrong and confusing combobox with drop-down list. But in your year test, you notice that you're using year_comboBox.Text. If there's a similar Value field, use that to set the month value.)

Upvotes: 2

Related Questions