• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 751
  • Last Modified:

Conversion of varchar data type to a datetime data type

Having that
SqlCommand myCommand = new SqlCommand("UPDATE UserTable SET lastactive='" + DateTime.Now + "' WHERE id=@id", myConnection);

getting this error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
0
doramail05
Asked:
doramail05
1 Solution
 
rushShahCommented:
It seems like, the length of "lastactive" varchar field is not sufficient to hold datetime value.

so try to increase the size of that field or give statastics of this field.
0
 
anillucky31Commented:
SqlCommand myCommand = new SqlCommand("UPDATE UserTable SET lastactive=" + DateTime.Now.ToString() + " WHERE id=@id", myConnection);
0
 
doramail05Author Commented:
anil,

having that
Incorrect syntax near '12'.

probably might not accept when there's no ' ' around the DateTime.Now

rushShah,
could find a place to increase that size
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Kevin CrossChief Technology OfficerCommented:
Think you have it backwards.  lastactive is DATETIME, correct?  I suspect the error is that the VARCHAR value which is what DateTime.Now is coming in as through the SQL command with the ' ' surrounding it.  What you will want to do is format the date properly.  Try sending in as 'yyyy-MM-dd hh:mm:ss'.  You can use the toString() method of .NET DateTime.Now.
0
 
ThomasianCommented:
SqlCommand myCommand = new SqlCommand("UPDATE UserTable SET lastactive='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE id=@id", myConnection);
0
 
ThomasianCommented:
Didn't refresh the page. Basically the same as mwvisa1's suggestion.
0
 
Kevin CrossChief Technology OfficerCommented:
SqlCommand myCommand = new SqlCommand("UPDATE UserTable SET lastactive='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE id=@id", myConnection);

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
*laughing* timing is everything.  That is two votes to ToString().  Hope that helps.
Regards,
Kevin
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now