C# Generate Query to Compare DateTime with SQL Nvarchar date Column

Question

I tried to get result depending on two dates which the user checked. I have two datetimepicker controls. I want the user to chooses the "from" date and "to" date, then the query get specific result.

leaving_time column type is nvarchar

This is my query:

SELECT name, mil_no, rotba, arrival_time, leaving_time, day, year
FROM dbo.Hodor_data 
WHERE leaving_time BETWEEN '"+dateTimePicker1.Checked.ToString()+ "' AND  '" + dateTimePicker2.Checked.ToString() + '"

Where is the mistake?

Possible duplicate of [datetime format to SQL format using C#](http://stackoverflow.com/questions/17418258/datetime-format-to-sql-format-using-c-sharp) — Stefano d'Antonio, Jul 09 '16 at 19:20
@amrkamal If what you need is the DateTime to a formatted SQL query, it's not. `dateTimePicker.Value.ToString("yyyy-MM-dd HH:mm:ss.fff")` is what you're after I guess. — Stefano d'Antonio, Jul 09 '16 at 19:24
@amrkamal Show SQL table data and show your entire code for executing this query. — Zein Makki, Jul 09 '16 at 19:34
I have the strong feeling that this _leaving_time_ is not a DateTime column. @amrkamal could you tell us what is the DataType of the column in the database table? — Steve, Jul 09 '16 at 19:42
Then you will have hard time to find dates. Dates should be stored in DateTime columns if you want to have any chance to execute queries on those values. — Steve, Jul 09 '16 at 20:01
but i think i can compare between two strings if i convert date to string is it true ? — amr kamal, Jul 09 '16 at 20:05
I don't know how do you store these dates in a varchar column, But to explain the problem suppose that you use a format of dd-MM-yyyy. Now your leaving_time is '01-07-2016' and the two picker values converted to strings are '15-04-2016' and '15-07-2016'. The leaving_time is not included in this period because alphabetically the letter '0' comes before of '1' . Dates should be used and stored as dates (internally they have a numeric representation that leaves out any possible misunderstanding) — Steve, Jul 09 '16 at 20:13
Look, you can do also with a CONVERT as explained below (forgetting to mention that this convert will wreak havoc with indexing), but there is no other correct way to do. Dates should be stored in some kind of DateTime column. — Steve, Jul 09 '16 at 20:21

Christos · Answer 1 · 2016-07-09T19:34:39.520

3

You should write parameterized queries and not using string concatenation for passing the parameters, in order to create a sql command. Using string concatenation makes you code vulnerable to sql injections.

var cmdText = @"SELECT ...
                FROM dbo.Hodor_data
                WHERE leaving_time BETWEEN @StartDate AND @EndDate";

var sqlCommand = new SqlCommand(cmdText, connection);
sqlCommand.Parameters.AddWithValue("@StartDate", dateTimePicker1.Value);
sqlCommand.Parameters.AddWithValue("@EndDate", dateTimePicker2.Value);

where connection is your sql connection object.

edited Jul 09 '16 at 19:34

answered Jul 09 '16 at 19:23

Christos

53,228
8
76
108

Checked is not a DateTime Property . – Zein Makki Jul 09 '16 at 19:23
That code doesnt use the Checked property, neither should you @user3185569. Just use the value - its a `DateTime` type – Ňɏssa Pøngjǣrdenlarp Jul 09 '16 at 19:24
@user3185569 for this reason I changed it to `Value`. Thanks – Christos Jul 09 '16 at 19:25
1

@Plutonix i think the user edited the answer after i posted my comment. Sorry for that. – Zein Makki Jul 09 '16 at 19:26
@amrkamal could you please debug your code, and check what is the value of `dateTimePicker1.Value` and the value of `dateTimePicker2.Value`? I assume that `leaving_time`'s data type is something like DATETIME. Correct? – Christos Jul 09 '16 at 19:32
minor typo: `sqlCommand.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = dateTimePicker1.Value;` – Ňɏssa Pøngjǣrdenlarp Jul 09 '16 at 19:33
1

@Plutonix I had forgotten it :) I would say to change it to `AddWithValue`. Thank you very much for pointing this ! – Christos Jul 09 '16 at 19:34
mistery solved. _leaving_time_ is a nvarchar(50) column. No chance to build correct queries in this condition – Steve Jul 09 '16 at 20:03
The code above will work when you change the Datatype of _leaving_time_ to DateTime – Steve Jul 09 '16 at 20:05
@amr can you show a sample value from *leaving_time* column ? – Zein Makki Jul 09 '16 at 20:06
6/24/2016 for example – amr kamal Jul 09 '16 at 20:09
@amrkamal solved then :) check my edited answer below. – Zein Makki Jul 09 '16 at 20:13

Mohamed · Answer 2 · 2016-08-30T09:24:12.460

2

Try to use dateTimePicker1.Text in dateTimePicker1_ValueChanged event where you are using dateTimePicker2.Checked that return true or false not the value of date

edited Aug 30 '16 at 09:24

answered Jul 09 '16 at 19:25

Mohamed

806
13
30

Zein Makki · Accepted Answer · 2016-07-09T20:38:38.700

1

Checked is a boolean property, and it is not the date. You need to use the Value Property. It is better to add parameters and explicitly specify the type so that the date format conflict is solved.

Edit: If column type in SQL server is NVARCHAR and of format MM/dd/yyyy, you need to use ONVERT(DATETIME, leaving_time, 101):

conn.Open();
SqlDataAdapter dataAdapter = 
    new SqlDataAdapter("SELECT name, mil_no, rotba, arrival_time, leaving_time, day, year "
                     + "FROM dbo.Hodor_data where CONVERT(DATETIME, leaving_time, 101) "
                     + "BETWEEN @p1 AND @p2", conn);

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

dataAdapter.SelectCommand.Parameters.Add(fromDate);
dataAdapter.SelectCommand.Parameters.Add(toDate);

DataTable dt = new DataTable();
dataAdapter.Fill(dt);
dataGridView1.DataSource = dt; 
conn.Close()

You should really consider changing the type of column leaving_time to be a DateTime column. This will make your life easier in querying. I can't really see any advantage of storing these values as text.

edited Jul 09 '16 at 20:38

answered Jul 09 '16 at 19:23

Zein Makki

29,485
6
52
63

@amrkamal can you show the entire code ? which is how you are executing the query and building the command object. – Zein Makki Jul 09 '16 at 19:33
conn.Open(); SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT name, mil_no, rotba, arrival_time, leaving_time, day, year FROM dbo.Hodor_data where leaving_time BETWEEN '" + dateTimePicker1.Value + "' AND '" + dateTimePicker2.Value.ToString("") + "'", conn); DataTable dt = new DataTable(); dataAdapter.Fill(dt); dataGridView1.DataSource = dt; conn.Close(); – amr kamal Jul 09 '16 at 19:58
The name 'dateParamFrom' does not exist in the current context !! – amr kamal Jul 09 '16 at 20:08
@amrkamal it will not work if your column type is nvarchar. Can you show a sample values from that column ? is it *2016-07-09* or *09-07-2016* ? – Zein Makki Jul 09 '16 at 20:09
@amrkamal typo fixed in the answer. – Zein Makki Jul 09 '16 at 20:10
@amrkamal check the updated code using `CONVERT(DATETIME` – Zein Makki Jul 09 '16 at 20:12
The variable name '@p1' has already been declared. Variable names must be unique within a query batch or stored procedure. – amr kamal Jul 09 '16 at 20:13
@amrkamal add a space after `year` in the query. – Zein Makki Jul 09 '16 at 20:22

C# Generate Query to Compare DateTime with SQL Nvarchar date Column

3 Answers3

Linked