Error: The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value

Question

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:

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

Can anyone help me out?

Answer 1

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

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

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).

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

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?

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

Answer 2

I had the same problem adding datetime.now into my SQL server column 'Date' set to datatype SmallDateTime.

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

Answer 3

Try changing this:

userQuizDataSource.InsertParameters.Add("DateTimeComplete", DateTime.Now.ToString());

to this:

userQuizDataSource.InsertParameters.Add("@startdate", SqlDbType.DateTime, DateTime.Now.ToString());

Answer 4

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.

Answer 5

In Windows 8, if you are facing this problem even after changing Formats in below location

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.