Click here to Skip to main content
15,745,335 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all have a datetimepicker and a datagridview in wins form, .Ideally I want a User to select date from datetimepicker and filtered data can show in datagridview row by row? My main purpose would like the user to pick up a date and return data fall in that date.But issue is my app is accessing an Access DB that was built and is used by another app and the date fields is in this Format
04/03/2015 11:23(dd/MM/yyyy mm:ss

whereas DateTimePicker is MM/DD/YYYY what can I do to achiever my goal ,below is the code am using in dateTimePicker Method to Filter my data, any suggestions?
DateTime dt = datetimepicker.Value;

//Connection string to connect to access database
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;";
using (var connection = new OleDbConnection(strConn))
    string strSql = String.Format("SELECT * FROM MYTABLE WHERE DateColumn = '{0}'", dt);
    using (var adap = new OleDbDataAdapter(strSql, connection))
        DataTable table = new DataTable();
        GridView1.DataSource = table;
Updated 3-Apr-15 19:29pm

1 solution

Have a look at the different ways to format a date string
Custom Date and Time Format Strings[^]

[EDIT] Added time information
string strSql = String.Format("SELECT * FROM MYTABLE WHERE DateColumn = '{0}'", dt.ToString("MM/dd/yyyy HH:mm"));

HH will give you values in 24 hour format.
hh will give you values in 12 hour format.
Share this answer
Ekona_Pikin 4-Apr-15 2:18am    
hi @George I still just get column headers with no data, Note in the Access DB DateColumn field is stored in this format ==> MM/dd/yyyy mm:ss
Ekona_Pikin 4-Apr-15 3:01am    
Hi@ George I did something like this and some data pulled in,but not filtering quite right ==> (WHERE DateColum1 = #{0:MM/dd/yy mm:ss}# AND DateColum2 #{0:MM/dd/yy mm:ss}#" , dt.ToString());
George Jonsson 4-Apr-15 3:24am    
What you have tried will not work because you first convert the DateTime variable to to a string and then you try to use date formatting on that string.
Use dt.ToString("MM/dd/yyyy HH:mm") instead and change {0:MM/dd/yy mm:ss} to {0}.
Also see my updated answer.
I also doubt that the time is in minutes and seconds. It is more likely that it is in hours and minutes.
Ekona_Pikin 4-Apr-15 6:52am    
Thanks you rock sir George, c# is fun again:))did what you said what happens is i kept using an HH instead of hh and reversed dt.String() nice touch sir I learned through this appreciative Happy weekend
George Jonsson 4-Apr-15 11:03am    
You are welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900