C# 错误：将 nvarchar 数据类型转换为 smalldatetime 数据类型导致值超出范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2307550/
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
Error: The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value
提问by c11ada
Hey all I'm trying to do the following insert query
嘿，我正在尝试执行以下插入查询
SqlDataSource userQuizDataSource = new SqlDataSource();
userQuizDataSource.ConnectionString = "Data Source=localhost\SQLEXPRESS;Initial Catalog=quizApp;Integrated Security=True";
userQuizDataSource.InsertCommand = "INSERT INTO [UserQuiz] ([DateTimeComplete], [Score], [UserName]) VALUES (@DateTimeComplete, @Score, @UserName)";
userQuizDataSource.InsertParameters.Add("DateTimeComplete", DateTime.Now.ToString());
userQuizDataSource.InsertParameters.Add("Score", score.ToString());
userQuizDataSource.InsertParameters.Add("UserName", User.Identity.Name);
int rowsAffected = userQuizDataSource.Insert();
Buti keep getting the following error:
Buti不断收到以下错误：
The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value. The statement has been terminated.
将 nvarchar 数据类型转换为 smalldatetime 数据类型导致值超出范围。该语句已终止。
Can anyone help me out?
谁能帮我吗？
采纳答案by marc_s
What does your statement DateTime.Now.ToString()return??
你的语句DateTime.Now.ToString()返回什么？
What language and regional settings is your SQL Server expecting??
您的 SQL Server 期望什么语言和区域设置？？
Do you have a mismatch there??? Maybe your .NET returns a MM/dd/yyyyformat, while SQL Server expects dd/MM/yyyy(or vice-versa).
你那里有不匹配吗？？？也许您的 .NET 返回一种MM/dd/yyyy格式，而 SQL Server 期望dd/MM/yyyy（或反之亦然）。
Try this code in your SQL Server:
在您的 SQL Server 中试试这个代码：
DECLARE @test TABLE (smalldate SMALLDATETIME)
INSERT INTO @test VALUES ('02/21/2010 22:00:32') --
SELECT * FROM @test
Replace my string there with what output you got from .NET's DateTime.Now.ToString()- does this work? Does SQL Server give you a better error message?
用你从 .NET 得到的输出替换我的字符串DateTime.Now.ToString()- 这行得通吗？SQL Server 是否为您提供了更好的错误消息？
Next, try to use the ISO-8601 format for dates (YYYYMMDD) - this works for ALLregional and language settings in SQL Server - does this work??
接下来，尝试使用 ISO-8601 格式的日期 (YYYYMMDD) - 这适用于 SQL Server 中的所有区域和语言设置 - 这是否有效？
DECLARE @test TABLE (smalldate SMALLDATETIME)
INSERT INTO @test VALUES ('20100221 22:00:32') --
SELECT * FROM @test
回答by Jim G.
Try changing this:
尝试改变这个：
userQuizDataSource.InsertParameters.Add("DateTimeComplete", DateTime.Now.ToString());
to this:
对此：
userQuizDataSource.InsertParameters.Add("@startdate", SqlDbType.DateTime, DateTime.Now.ToString());
回答by ziya
Try no converting your date to string:
尝试不要将您的日期转换为字符串：
userQuizDataSource.InsertParameters.Add("DateTimeComplete", DateTime.Now);
Edit:Try this then:
编辑：然后试试这个：
userQuizDataSource.InsertParameters.Add("DateTimeComplete", TypeCode.DateTime, DateTime.Now.ToString());
There is another way to just pass the actual object, but I can't remember.. sorry.
还有另一种方法可以传递实际对象，但我不记得了……抱歉。
回答by Liam
I had the same problem adding datetime.now into my SQL server column 'Date' set to datatype SmallDateTime.
我在将 datetime.now 添加到设置为数据类型 SmallDateTime 的 SQL 服务器列“Date”中时遇到了同样的问题。
To resolve it was quite simple (after many attempts!!)
解决它很简单（经过多次尝试！！）
string currentdatetime=
DateTime.Now.Year + "." + DateTime.Now.Month + "." + DateTime.Now.Day +
" " + DateTime.Now.Hour+(":")+DateTime.Now.Minute+(":")+DateTime.Now.Second
This will return the date into the format that the Server will expect
这会将日期返回为服务器期望的格式
回答by Jasim Khan Afridi
In Windows 8, if you are facing this problem even after changing Formatsin below location
在 Windows 8 中，如果您Formats在以下位置更改后仍面临此问题
Control Panel -> Region
控制面板 -> 区域
You still have to transfer these settings to your users. In the same window, go to tab "Administrative", click on copy settings.
您仍然需要将这些设置传输给您的用户。在同一窗口中，转到“管理”选项卡，单击复制设置。
Select the appropriate check boxes and click OK.
选择适当的复选框并单击OK。
