How to get data between two dates using DateTimePicker?

Question

Hiii Seems it's too common and million times asked on the net and lots of answers are there but none works for me. so, please take a look.   >Windows Application using VS2008 >Access Database. Data Type = Date/Time, Format = Short Date.   Two DateTimePickers and a button's click event. I just want to get records between two dates INCLUDING the dates in the DTP. I have searched and found different select statements to fetch data based on two dates. 1. OleDbDataAdapter FetchBillList = new OleDbDataAdapter("Select BillSrNo, BillPatCase, BillPatName, BillAmtPaid From Billings Where BillDate Between'" + dtpFromDt.Value + "' AND '" + dtpTodt.Value + "'", MyConnAcc); //Error - Data type mismatch in criteria expression.   2.OleDbDataAdapter FetchBillList = new OleDbDataAdapter("Select BillSrNo, BillPatCase, BillPatName, BillAmtPaid From Billings Where BillDate >='" + dtpFromDt.Value.ToShortDateString() + "' AND BillDate //Error - Data type mismatch in criteria expression.   3.OleDbDataAdapter FetchBillList = new OleDbDataAdapter("Select BillSrNo, BillPatCase, BillPatName, BillAmtPaid From Billings Where BillDate Between#" + dtpFromDt.Value.ToShortDateString() + "# AND #" + dtpTodt.Value.ToShortDateString() + "#", MyConnAcc); //This query does not show any error but also not fetching proper data. It gives all the records regardless mentioned in the DTP.   4.Also used parameterized query but no result.    I have searched on net but no answer matches my requirement.   Please help.   Mayank

Mayank Jani · Answer

Hiii   I have get it done by using TextBox instead of DateTimePicker. It's easy.   Thanks for your time and support.   Mayank Jani

Mayank Jani · Answer

Hi,   I am using VS2008, working on a Windows application. Database is MS Access 2007 in WinForm, I have two DateTimePicker and one Button. Resulting data shows in a DataGridView. in DataTable, field DataType is DateTime and Format is set to ShortDate (14/08/2018)   Thanks

Jignesh Kumar · Answer

could you please share mark up and table structure for that two fields. ? That will help to understand problem

Mayank Jani · Answer

Hiii Jignesh Kumar,   Thank you for your reply... Yessss, you're right, I have asked this question in Sep, 2017 but, It was answered but did not fulfill my requirement so, I have to keep that on hold and start working on other modul of the project.   Ummm... no it's not working as you suggested. It gives error, 'String was not recognized as a valid DateTime.'   Please tell me what to do. if you want any other information, please let me know.   Thanks.

Jignesh Kumar · Answer

Hi Mayank,   I think you have posted this question few days ago. If I am not wrong, Please try below ParseExaxt() method   DateTime fromDate = DateTime.ParseExact(Convert.ToString(dtpFromDt.Value),"yyyy-MM-dd", null); DateTime toDate = DateTime.ParseExact(Convert.ToString(dtpTodt.Value.Value),"yyyy-MM-dd", null); OleDbDataAdapter FetchBillList = new OleDbDataAdapter("Select BillSrNo, BillPatCase, BillPatName, BillAmtPaid From Billings Where BillDate Between#" + fromDate + "# AND #" + toDate + "#", MyConnAcc);