字符串未被识别为Sql服务器的有效DateTime C＃

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

我将日期时间值从C#传递到SQL Server SP。如果我传递格式dd-MM-yyyy然后它工作正常但没有从SP返回任何值,即使有该日期的记录。如果我使用格式MM-dd-yyyy运行SP,则它返回错误“将数据类型nvarchar转换为datetime时出错”。 SP是

execute usp_detData '22/12/2012 00:00:00', '31/12/2013 23:59:59' --- error

执行usp_detData '22 / 12/2012 00:00:00','31 / 12/2013 23:59:59'---错误

execute usp_detData '12/22/2012 00:00:00', '12/31/2013 23:59:59' --- working fine

执行usp_detData '12 / 22/2012 00:00:00','12 / 31/2013 23:59:59'---工作正常

Would you please let me the the solution

你能告诉我解决方案吗?

3 个解决方案

#1

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

我将日期时间值从C#传递到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.

我相信你通过字符串连接传递它。如果你使用DateTime类型的SqlParameter并让服务器处理它,它会更好。

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.

还要考虑在代码中使用using块来实现IDisposable。例如。 SqlCommand,SqlConnection,SqlDataReader等。

#2

Try this :

试试这个 :

DateTime.ParseExact("12/02/21 10:56:09", "dd/MM/YYYY HH:mm:ss", 
    CultureInfo.InvariantCulture
    ).ToString("MMM. dd, yyyy HH:mm:ss")

or go through this question Convert DateTime to a specified Format

或者通过这个问题将DateTime转换为指定的格式

#3

Use the format 'yyyy-MM-dd HH:mm:ss' and things will all be fine.

使用格式'yyyy-MM-dd HH:mm:ss',事情一切都会好起来的。

#1