任何月份的12日以上的日期的算术溢出错误

I have created a table in MS SQL 2008 which is of type "datetime".

我在MS SQL 2008中创建了一个类型为“datetime”的表。

In front end i have created a input box which reads the date from calendar control.

在前端，我创建了一个输入框，从日历控件中读取日期。

As soon as user selects the date from calendar control, input box is populated with selected date.

用户从日历控件中选择日期后，输入框将填充所选日期。

TextBox1.Text = Calendar1.SelectedDate.ToString("dd/MM/yyyy");

then i am adding the contents like as below to SQL Command

然后我将如下的内容添加到SQL命令

cmd.Parameters.AddWithValue("@date", TextBox1.Text);
cmd.ExecuteNonQuery();

This code works fine when I select dates till 11th, i.e. 11-04-2013, as soon i select 12-04-2013 and any date above 12th of any month, I am getting exception "Arithmetic overflow error converting expression to data type datetime"

当我选择日期到11日，即11-04-2013时，此代码工作正常，我选择12-04-2013以及任何月份的12日以上的任何日期，我得到异常“算术溢出错误将表达式转换为数据类型datetime “

Let me know where I have gone wrong.

让我知道我哪里出错了。

2 个解决方案

#1

Directly add date to parameter, you don't have to format it.

直接向参数添加日期，您不必格式化它。

cmd.Parameters.AddWithValue("@date",  Calendar1.SelectedDate);

SqlServer DateTime column in irespective of format. The reason you are getting the overflow is because it is considering first part as Month and since there are only 12 months in an year, you are getting the error. Formatting is only useful for displaying dates.

SqlServer DateTime列，与格式无关。你得到溢出的原因是因为它将第一部分视为月份而且因为一年只有12个月，所以你得到了错误。格式化仅对显示日期有用。

#2

This might be due to the current culture your system is using. I believe SQL Server expected date in the form of yyyy-MM-dd and somehow your code is interchanging day with month

这可能是由于您的系统正在使用的当前文化。我相信SQL Server的预期日期是yyyy-MM-dd的形式，不知怎的，你的代码每天都在和月交换

Try

尝试

cmd.Parameters.AddWithValue("@date", String.Format("{0:yyyy-MM-dd}", TextBox1.Text));

Or convert to date

或转换为日期

cmd.Parameters.AddWithValue("@date", Convert.ToDateTime(TextBox1.Text));

#1