amr kamal amr kamal - 1 year ago 126
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?

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download