amr kamal amr kamal - 3 months ago 17x
SQL Question

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

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?


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):

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;


DataTable dt = new DataTable();
dataGridView1.DataSource = dt; 

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.