String was not recognized as a valid DateTime C# to Sql server

c#, sql-server

Solution

I am passing a datetime value from C# to SQL Server SP.

I believe you are passing it via string concatenation. Its better if you use SqlParameter of type `DateTime` and let the server handle it.

using (SqlCommand cmd = new SqlCommand(" usp_detData", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@StartDate", DateTime.Now.AddDays(-10));
    cmd.Parameters.AddWithValue("@EndDate", DateTime.Now);
    SqlDataReader dr = cmd.ExecuteReader();
    .....
}

Also consider using the using block in your code with objects which implements IDisposable. for example. `SqlCommand`, `SqlConnection`, `SqlDataReader` etc.

Problem

I am passing a datetime value from C# to SQL Server SP. If I pass the format `dd-MM-yyyy` then it is working fine but not returning any values from SP even there are records of that date. If I run the SP with the format `MM-dd-yyyy`, then it is returning the error "Error converting data type nvarchar to datetime.". The SP is `execute usp_detData '22/12/2012 00:00:00', '31/12/2013 23:59:59'` --- error `execute usp_detData '12/22/2012 00:00:00', '12/31/2013 23:59:59'` --- working fine Would you please let me the the solution

Original source

Related problems