retrieve records between dates selected from datetimepicker and of selected date

[luvkush]

i want to retrieve the records selected between from_dt and to_dt and also the records of the selected date should be retrieved...

SELECT ChallanDtl.chalID, ChallanDtl.chalTo, 
ChallanDtl.chalNo, ChallanDtl.chalDDate,
ChallanDtl.chalYourNo, ChallanDtl.chalPO_NO, 
ChallanDtl.chalPO_Date, 
ChallanSubDtl.chalsubdescription, 
ChallanSubDtl.chalsubQty  FROM ChallanDtl INNER JOIN 
ChallanSubDtl ON ChallanDtl.chalID = 
ChallanSubDtl.ChalID where ChallanDtl.chalPO_Date >= '" 
+ frm_dtpicker.value + "' and ChallanDtl.chalPO_Date 
<='" + to_dtpicker.value + "'", cn);

this is my query please reply.. me with the query as soon as possible with the above query i m not getting all the records.... when i select the same date in both datetimepicker no record is displayed... i also want the record of the selected dates from both datetime picker..

[Rabbit]

We need additional information.

1) Output of the SQL string that it's trying to execute
2) The metadata of the table
3) The name of the database engine you're using

[arie]

When user picks date in DateTimePicker, it's Value property is set to DateTime.Now. This means that, aside from 'date' value, you have also picked 'time' value. Also, when you're using DateTimePicker.Value to construct sql query in a way shown in your code, what you really get is DateTimePicker.Value.ToString(), which depends on property Format of DateTimePicker and CultureInfo set for your system/thread/application. Since date and time are displayed differently in different countries (e.g. sometime monts and days switch places), using value of DateTime without first formatting it (so your database engine understands) isn't very safe.

I don't know what database you're using, but most databases will understand querries correctly if you use this to format datetime:

DateTime fromDate = frm_dtpicker.Value;
string formatedDateTimeString = String.Format("{0:yyyy-MM-dd HH:mm:ss}", fromDate);

Regarding your other problem (query is not getting all the records): to choose a period of time properly, you simply need to choose (midnight of fromDate, midnight of toDate + 1 day), so:

DateTime fromDate = frm_dtpicker.Value.Date;
DateTime toDate = to_dtpicker.Value.Date.AddDays(1);

Then, if the date chosen in frm_dtpicker is the same as the is the date chosen in to_dtpicker, e.g "2012-03-31", you should get 24 hours:

fromDate: 2012-03-31 00:00
toDate: 2012-04-01 00:00