C# 将 varchar 数据类型转换为 datetime 数据类型导致值超出范围错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12393665/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value error
提问by Aaron Mohammed
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value error
将 varchar 数据类型转换为 datetime 数据类型导致值超出范围错误
I'm trying to enter data into my table using a form, the date formats in both the form validation and the sql server are both dd/mm/yy, however when i try to submit data from the form with a day higher than 12 (e.g. 13/12/2012) it throws an exception whose cause is "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value error", and if i try to enter data into the form in a mm/dd/yy format it states "wrong date format" which is suppose means that the dd/mm/yy format is the correct format
我正在尝试使用表单将数据输入到我的表中，表单验证和 sql server 中的日期格式都是 dd/mm/yy，但是当我尝试从表单提交数据时，一天大于 12 （例如 13/12/2012）它抛出一个异常，其原因是“将 varchar 数据类型转换为日期时间数据类型导致值超出范围错误”，如果我尝试将数据输入表单在 mm/dd/yy 格式中，它声明“错误的日期格式”，这意味着 dd/mm/yy 格式是正确的格式
here's the code for my form below:
下面是我的表单的代码：
private void btnAddProject_Click(object sender, EventArgs e)
{
DateTime startDate;
DateTime endDate;
if (txtProjectName.Text == "") //client side validation
{
MessageBox.Show("Enter Project Name");
return;
}
try
{
startDate = DateTime.Parse(txtProjectStart.Text);
endDate = DateTime.Parse(txtProjectEnd.Text);
}
catch (Exception)
{
MessageBox.Show("Wrong Date Format");
return;
}
fa.CreateProject(txtProjectName.Text, startDate, endDate, (int)cbCustomers.SelectedValue, ptsUser.Id);
txtProjectName.Text = "";
txtProjectStart.Text = "";
txtProjectEnd.Text = "";
cbCustomers.SelectedIndex = 0;
MessageBox.Show("Project Created");
adminControl.SelectTab(2);
}// end btnAddProject
And this is the code in my DAO:
这是我的 DAO 中的代码：
public void CreateProject(string name, DateTime startDate, DateTime endDate, int customerId, int administratorId)
{
string sql;
SqlConnection cn;
SqlCommand cmd;
Guid projectId = Guid.NewGuid();
sql = "INSERT INTO Project (ProjectId, Name, ExpectedStartDate, ExpectedEndDate, CustomerId, AdministratorId)";
sql += String.Format("VALUES('{0}', '{1}', '{2}', '{3}', {4}, {5})", projectId, name, startDate, endDate, customerId, administratorId);
cn = new SqlConnection(Properties.Settings.Default.WM75ConnectionString);
cmd = new SqlCommand(sql, cn);
try
{
cn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception("Error Creating Project", ex);
}
finally
{
cn.Close();
}
}//end CreateProject Method
This is my code for my facade:
这是我的门面代码：
public void CreateProject(string name, DateTime startDate, DateTime endDate, int customerId, int administratorId)
{
dao.CreateProject(name, startDate, endDate, customerId, administratorId);
}//end CreateProject
采纳答案by Jon Skeet
Basically, you shouldn't be passing the DateTimevalues as stringsin your SQL at all. Use parameterized SQL, and just set the parameter value directly. You should alwaysuse parameterized SQL as far as possible:
基本上，您根本不应该在 SQL中将DateTime值作为字符串传递。使用参数化SQL，直接设置参数值即可。您应该始终尽可能使用参数化 SQL：
- It gives better code/data separation
- It avoids problematic conversions (like this one)
- It avoids SQL injection atttacks
- 它提供了更好的代码/数据分离
- 它避免了有问题的转换（比如这个）
- 它避免了 SQL 注入攻击
Additionally, your exception handling is pointlessly complex. Just use a usingstatement, and let the SqlExceptionbubble up directly - why bother wrapping it in a vanilla Exception?
此外，您的异常处理是毫无意义的复杂。只需使用一个using语句，然后SqlException直接让气泡冒出来——为什么要把它包在香草里Exception呢？
回答by PQubeTechnologies
sql = "INSERT INTO Project (ProjectId, Name, ExpectedStartDate, ExpectedEndDate, CustomerId, AdministratorId)";
sql += String.Format("VALUES('{0}', '{1}', '{2}', '{3}', {4}, {5})", projectId, name, startDate.toString("dd-MMM-yyyy"), endDate.toString("dd-MMM-yyyy"), customerId, administratorId);
A suggestion : why are you using Insertstatement in your code? You can have a stored procedure instead.
一个建议：你为什么Insert在你的代码中使用语句？您可以改为使用存储过程。
回答by Baskaran
You can do sqldatetime conversion like sqldatetime
您可以像sqldatetime一样进行 sqldatetime 转换
var sqlFormattedDate = myDateTime.Date.ToString("yyyy-MM-dd HH:mm:ss");
