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

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.

Hello , Sai Yang

When You want to store date and time value from date time picker control to sql data table where date/time column type is declared as Nvarchar , You need to format it to nvarchar, that means simple string like ( "YYYY-MM-DD-HH-MM-SS"). Write Your own method for that with Your own defined string format for date and time so that You prevent any problems with different system date time settings at different computers running Your program. You can use standard ToString() method for that. When You want to use such date and time value from sql data table just load it as simple string value and then use again Your own method for extracting year, month, day....

If You need to store date and time to sql data table at sql server where date/time column type is declared as datetime , You need to know exact time and date format that sql table accepts and computer system date time regional settings. Usually they are different. In that case there is need to format date and time value from datetimepicker to date time format recognizable by the sql data server and acceptable by sql data table.

If there is no need for sorting or selecting values inside sql data table by date/time column the Nvarchar type value is better solution.

https://msdn.microsoft.com/en-us/library/system.datetime(v=vs.110).aspx

All the best,

Željko Peric

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Željko Perić8y ago
Hello , Sai Yang
When You want to store date and time value from date time picker control to sql data table where date/time column type is declared as Nvarchar , You need to format it to nvarchar, that means simple string like ( "YYYY-MM-DD-HH-MM-SS"). Write Your own method for that with Your own defined string format for date and time so that You prevent any problems with different system date time settings at different computers running Your program. You can use standard ToString() method for that. When You want to use such date and time value from sql data table just load it as simple string value and then use again Your own method for extracting year, month, day....

If You need to store date and time to sql data table at sql server where date/time column type is declared as datetime , You need to know exact time and date format that sql table accepts and computer system date time regional settings. Usually they are different. In that case there is need to format date and time value from datetimepicker to date time format recognizable by the sql data server and acceptable by sql data table.

If there is no need for sorting or selecting values inside sql data table by date/time column the Nvarchar type value is better solution.

https://msdn.microsoft.com/en-us/library/system.datetime(v=vs.110).aspx

All the best,
Željko Peric