G ABBAS G ABBAS - 4 months ago 11
C# Question

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

My data is saved in this format

mm/dd/yyyy
... 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");
con.Open();

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();
sda.Fill(dt);

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

dataGridView1.DataSource = bs;
sda.Update(dt);

con.Close();

Answer

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 :

cmd.Parameters.AddWithValue("@Date_Of_Something",this.dateTimePicker1.Value.Month);

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 :

cmd.Parameters.AddWithValue("@DatePicker_Month",this.dateTimePicker1.Value.Month);
cmd.Parameters.AddWithValue("@DatePicker_Year",this.dateTimePicker1.Value.Year);

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!!!