[C#] How to select to report by day, month and year ???

Question

I try using this code to select date from database to pair with dateTimepicker, but it does not work correctly for me. Seems it compair like a string. At first I my date's datatype is Nvarchar I select and convert like:

select tb_product.is_stockdate from tb_product where '"+CONVERT.ToDatetime("tb_product.is_stockdate")+"' ='"+dateTimepicker1.Value+"'

but it every kid to select like this statement.

 

it does not work for me, then I change datatype to date. It keeps the same result and then I change to datetime and giving the same result.

 

I code like here:

insert statement:
insert into tb_product(Name, QTY,Price,is_stockdate) Value('"+txtname.Text+"','"+txtqty.Text+"','"+txtprice.Text+"','"+dateTimepicker1.Value+"')

CASE 1:

 

select to report statement between two dates IS:

 

now datatype is date. 

Select tb_product.Name,tb_product.QTY,Tb_product.Price,tb_product.is_stockdate from tb_product where tb_product.is_stockdate>='"+dateTimepicker1.Value+"' and tb_product.is_stockdate<='"+dateTimepicker2.Value+"';

this case is give me all the data. Seem 'LIKe' operant but not only between two dates. It is from everywhere has number like the datetimepicker value.

 

CASE 2 and CASE 3:

 

to report by month or Year.

 

I am not sure to select it. I try to convert to datetimerpick value but it is not done.

 

I found a VB.Net code toconvert datetimepicker

Dim year1 = Format(DateTimePicker1.Value, "yyyy-MM-dd").Substring(0, 4)

select statement:

select sum(Price) from printitems where left(paydate,4)='" & year1 & "';

this is correctly work for vb.net by month and year(just change number 4 to 7 for month).

The case 2&3 is very important to my own case now, but how to work with it?

 

In this question I need to know how to convert datetimepicker to new format?

 

In select statement: How to compare the database date to datetimepicker value with correctly?

 

I now want to report by month and Year. I try in many case but it is done.

 

belong to my code mention above how to select with correct statement Or how to change to correct format.

 

Please share and help me. I just create a graduate project in Bachelor degree.

 

thank you for helpfullness idea.