G ABBAS G ABBAS - 1 year ago 43
C# Question

How to compare mm/dd/yyyy with month only in SQL Server database using C#

My data is saved in this format

... and I want to compare my data with month only....I mean I want to show all data with respect to that month selected by datetimepicker.

SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Coregen\documents\visual studio 2013\Projects\piechart\piechart\sale.mdf;Integrated Security=True");

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM Tab_n WHERE Date='" + this.dateTimePicker1.Value + "'"; //
cmd.Connection = con;

SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = cmd;
DataTable dt = new DataTable();

BindingSource bs = new BindingSource();
bs.DataSource = dt;

dataGridView1.DataSource = bs;


Answer Source

You can use something along these lines :

 cmd.CommandText = "SELECT Column_1, Column_2 FROM Tab_n WHERE DATEPART(Month,Date) ='" + this.dateTimePicker1.Value.Month + "'";

You can simplify this by using SQLParameter :


Edit : Since you want to compare year too, you will have to add another where condition. This will again make use of the DATEPART function.

Since I presume by now you must have taken up on the suggestion of using parameterized queries, the following addendum is answered with that approach in mind.

cmd.CommandText = "SELECT Column_1, Column_2 FROM Tab_n WHERE DATEPART(Month,Date) = @DatePicker_Month AND DATEPART(Year,Date) = @DatePicker_Year";

You will have two parameters in this case :


This, for example, will allow you to fetch results for the 5th month (specified by datepart_month variable) for the year 2016 (specified by datepart_year variable).

Hope this helps!!!