DateTime conversion failed when converting date and/or time from character string

1
votes

I am trying to run the following simple SQL Query that selects data between two dates. The dates come from the following DateTimePickers: DTP_From, DTP_To

        DateTime startDate = DTP_From.Value.Date;
        DateTime endDate = DTP_To.Value.Date;
        SqlConnection con = new SqlConnection(strConnection);
        con.Open();
        SqlCommand sqlCmd = new SqlCommand();
        sqlCmd.Connection = con;
        sqlCmd.CommandType = CommandType.Text;
        sqlCmd.CommandText = "SELECT * From Report_Sales where Date >= '" + startDate + "' AND Date <= '" + endDate + "'";

When the query is executed I get the following error:

datetime conversion failed when converting date and/or time from character string

How can I run the above query error free?

2
c#sqlwinforms
I would like to prefer use "Between" keyword instead of >= and <= operator – Shell
convert date into 'yyyy-MM-dd HH:mm' format. and make sure the time of start date must be 00:00 and 23:29 for endDate otherwise some record will not be displayed. – Shell

2 Answers

3
votes

Parameterize your query to avoid issues like this.

sqlCmd.CommandText = "SELECT * From Report_Sales where Date >= @startDate AND Date <= @endDate";
sqlCmd.Parameters.AddWithValue("@startDate", startDate);
sqlCmd.Parameters.AddWithValue("@endDate", endDate);
0
votes

If you don't follow Grant Winney's excellent advice to parametise your queries, which will also help you avoid other nasties; to include DateTime data into a Transact-SQL query it needs to be formatted using the ISO 8601 style:

YYYY-MM-DDThh:mm:ss.nnn[Z]

ie

SELECT *
FROM Report_Sales
WHERE Date >= '2014-04-18T21:26:01Z'
AND Date <= '2014-04-18T22:26:01Z'