up vote 0 down vote favorite

Currently I've a string with a date and time. When I show the string it looks like:

2015-06-16 09:17:28 PM

But when I try to put it into the database it's telling me:

Additional information: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

I've to convert the string. So there is now other way!

What's the right way to do this? Obviously the value in database is datetime.

This is my code (I've put it together actually it's from multiple classes):

    string time = date + " " + txtTime.Text;
    DateTime temp = Convert.ToDateTime(time);
    String query ="insert into reserveringen (reserveringId,klantId,medewerkerId,aantalPersonen,begintijd,eindtijd)values(@reserveringId,@klantId,@medewerkerId,@aantalPersonen,@begintijd,@eindtijd)";
    SqlCommand comm = sqlCrud.returnSqlCommand(query);
    comm.Parameters.AddWithValue("begintijd",temp);

I already googled a lot.... but nothing works.

Thanks

share|improve this question
    
Try DateTime temp = DateTime.Parse(time);. – EBrown Jun 16 '15 at 19:30
1  
I don't know the language... but could it be eindtijd that is the problem, and not begintijd? We don't see what you're specifying for that value. (I'm basing the question on the suffix assuming they both mean date :P) – Kritner Jun 16 '15 at 19:34
1  
When you do AddWithValue, if you replace temp with DateTime.Now just as a test. Does that work? – sstan Jun 16 '15 at 19:35
1  
You can and should debug temp. Also, I'm not sure if those params work without the @. – Henk Holterman Jun 16 '15 at 19:35
2  
also... shouldn't all of the values you're inserting be parameters? Where else would the values come from? from your statement it only looks like one value in your statement is a parameter - @reserveringId. – Kritner Jun 16 '15 at 19:41
up vote 1 down vote accepted

You should not use Convert.ToDateTime like that. You should use DateTime.Parse or DateTime.ParseExact.

DateTime temp = DateTime.ParseExact(time, "yyyy-MM-dd hh:mm:ss tt", CultureInfo.InvariantCulture);

Since DateTime.Parse(temp) worked for you, you can leave it as that.

Edit:

The difference between the two methods is very important in this situation.

Convert.ToDateTime(s)

This method will call DateTime.Parse internally with the following parameters:

DateTime.Parse(s, DateTimeFormatInfo.CurrentInfo, DateTimeStyles.None);

Now, DateTime.Parse(s) does something a little different:

DateTime.Parse(s, DateTimeFormatInfo.CurrentInfo, DateTimeStyles.AllowWhiteSpaces);

The difference is the DateTimeStyles flag. This flag completely changes the output. The Convert.ToDateTime(s) was likely returning a DateTime.MinValue object, which is what it does when it encounters a null string. (I can only guess without spending more time on research, but that result makes sense, as DateTime.MinValue is 1/1/0001 12:00:00 AM, which is outside the range SQL expects.)

References:

http://bytes.com/topic/c-sharp/answers/482419-convert-todatetime-vs-system-datetime-parse

https://msdn.microsoft.com/en-us/library/system.datetime.parse(v=vs.110).aspx

https://msdn.microsoft.com/en-us/library/system.datetime.minvalue(v=vs.110).aspx

share|improve this answer
    
Thanks for the help – Jamie Jun 16 '15 at 20:05
    
@jamie Not a problem - glad I could assist you. :) – EBrown Jun 16 '15 at 20:05
up vote 0 down vote

Both 

DateTime.Parse("2015-06-16 09:17:28 PM");

And

Convert.ToDateTime(date);

will return the same DateTime value.

I would try to focus on the query.

Note that you might be passing a value from C# that resolve in the minimum value for DateTime (1/1/0001 12:00:00 AM) which is out of range for DateTime value on SQL Server.

share|improve this answer
    
Thanks for answering. Already solved the problem. I added DateTime temp = DateTime.Parse(time); and it worked! – Jamie Jun 16 '15 at 19:54
    
@AlbertMori That is not true. Convert.ToDateTime(s) calls DateTime.Parse(s, DateTimeFormatInfo.CurrentInfo, DateTimeStyles.None). That last part is very important - whitespaces will do strange things. DateTime.Parse(s) uses DateTime.Parse(s, DateTimeFormatInfo.CurrentInfo, DateTimeStyles.AllowWhiteSpaces). – EBrown Jun 16 '15 at 20:05
    
You are correct @EBrown, however given the string above this unit test passes without issues. [TestMethod] public void DateTimeTest() { var date = "2015-06-16 09:17:28 PM"; var data = DateTime.Parse(date); var data2 = Convert.ToDateTime(date); Assert.IsTrue(data == data2); } – Albert Mori Jun 16 '15 at 20:41
    
@AlbertMori Regardless of the Unit Test, changing Convert.ToDateTime to DateTime.Parse worked for the OP. Perhaps there is something else in there we know not of. – EBrown Jun 17 '15 at 1:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.