How to select datetime2 from Windows forms DateTimePicker

Question

I am trying to sort my database data in datagridview using DateTimePicker control , the type of date column in my Event_test table is datetime2

SqlDataAdapter sda = new SqlDataAdapter("SELECT * from Event_test where date between '"
                     +dateTimePicker1.Value.ToString()+"' AND'"+dateTimePicker2.Value.ToString()+ "'", con);
            DataTable data = new DataTable();
            sda.Fill(data);
            dataGridView1.DataSource = data;

This solution doesn't work properly , it sorts data wrongly at first time and when i change time pickers this error occurs :

Conversion failed when converting date and/or time from character string

Any help is appreciated.

So how shall I adjust the select statement to get what I need?

Looks like dateTimePicker1 and dateTimePicker2 are both unchangeable ; they are set to 01/10/2016 and 10/10/2016 and whenever i change dates it shows me results between those dates !!

Zein Makki · Answer 1 · 2016-07-09T20:14:02.847

0

You really need to format your date string to match the format of SQL-Server:

string sqlFormat = "yyyy-MM-dd HH:mm:ss.fff";

String query = "SELECT * from Event_test where date between '"
            + dateTimePicker1.Value.ToString(sqlFormat) + "' AND'" 
            + dateTimePicker2.Value.ToString(sqlFormat) + "'";

Better option is to use Sql Parameters (try to avoid query strings concatenation):

var sda = new SqlDataAdapter("SELECT * from Event_test where [date] between @p1 AND @p2", con);

SqlParameter dateParamFrom = new SqlParameter("@p1", SqlDbType.DateTime2);
dateParamFrom.Value = dateTimePicker1.Value;
SqlParameter dateParamTo = new SqlParameter("@p2", SqlDbType.DateTime2);
dateParamTo.Value = dateTimePicker2.Value;

sda.SelectCommand.Parameters.Add(dateParamFrom);
sda.SelectCommand.Parameters.Add(dateParamTo);

edited Jul 09 '16 at 20:14

answered Jul 09 '16 at 15:03

Zein Makki

29,485
6
52
63

I tried this solution , but it always shows me wrong results :( – Ahmed Aekbj Jul 09 '16 at 16:48
@AhmedAekbj update your question and show sample data from your table, and sample date and time, the expected output and the wrong output. – Zein Makki Jul 09 '16 at 17:14
Looks like `dateTimePicker1` and `dateTimePicker2` are both unchangeable ; they are set to `01/10/2016 and `10/10/2016 and whenever i change dates it shows me results between those dates !! – Ahmed Aekbj Jul 09 '16 at 17:52
@AhmedAekbj just debug the generated query before execution and check what it is. Then you can see where the problem is. – Zein Makki Jul 09 '16 at 17:54
The solution you gave me works perfect , the problem now is : that `dateTimePicker`s are never changed – Ahmed Aekbj Jul 09 '16 at 17:56
@AhmedAekbj well this is not a reason to remove the mark as answered from this. This answers your original question on *how to pass a datepicker date value to SQL server ?* If you have another question related to issues with datetimepicker, you need to open another question and provide additional details on that matter.. A Question like *DateTimePicker provides a wrong values when Value Property accessed* If that is really the issue. – Zein Makki Jul 09 '16 at 19:15
@AhmedAekbj code fixed. Check it now. issue was that we used same parameter name for both dates. – Zein Makki Jul 09 '16 at 20:15

How to select datetime2 from Windows forms DateTimePicker

1 Answers1