错误：一个nvarchar数据类型为SMALLDATETIME数据类型的转换导致超出范围的值 [英] Error: The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value

问题描述

嘿所有我想要做以下插入查询

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:

一个nvarchar数据类型的转化   以产生一个SMALLDATETIME数据类型   在超出范围的值。   该语句已终止。

The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value. The statement has been terminated.

谁能帮我？

推荐答案

什么是您的发言 DateTime.Now.ToString（）返回??

What does your statement DateTime.Now.ToString() return??

什么语言和区域设置为您的SQL Server期待？

What language and regional settings is your SQL Server expecting??

你有一个不匹配有???也许你的.NET返回 MM / DD / YYYY 格式，而SQL Server需要 DD / MM / YYYY （或反之亦然）。

Do you have a mismatch there??? Maybe your .NET returns a MM/dd/yyyy format, while SQL Server expects dd/MM/yyyy (or vice-versa).

试试这个code在您的SQL Server：

Try this code in your SQL Server:

DECLARE @test TABLE (smalldate SMALLDATETIME)
INSERT INTO @test VALUES ('02/21/2010 22:00:32') --
SELECT * FROM @test

替换字符串我和你一起从.NET的得到了输出 DateTime.Now.ToString（） - 工作的呢？有没有SQL服务器给你一个更好的错误信息？

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?

其次，尽量使用ISO-8601格式的日期（年月日） - 这适用于所有 SQL Server中的区域和语言设置 - 做这项工作？

Next, try to use the ISO-8601 format for dates (YYYYMMDD) - this works for ALL regional and language settings in SQL Server - does this work??

DECLARE @test TABLE (smalldate SMALLDATETIME)
INSERT INTO @test VALUES ('20100221 22:00:32') --
SELECT * FROM @test

这篇关于错误：一个nvarchar数据类型为SMALLDATETIME数据类型的转换导致超出范围的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！