Error in inserting datetime in ms sql..

_{Sunday, June 28, 2009 8:04 PM}

Hi,

 I am trying to insert datetime.Now value in MS SQL datetime field and i am getting following error message:
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated."

My Insert query is as follows:
string insertother = "INSERT INTO casecomments (userID, enquiryID, caseID, comment, regdate, usertype) VALUES ('" +Convert.ToInt32(Session["uid"]) + "','" + Convert.ToInt32(Request.QueryString["enqID"]) + "','" + Convert.ToInt32(Request.QueryString["cID"]) + "','" + txt_comment.Text + "','" + DateTime.Now + "','staff')";

Please let me know how can i solve this problem.Vinayak Nirvani.

_{Sunday, June 28, 2009 8:21 PM ✅Answered}

1. Don't use string concatenation - use parameters.

2. Your query could be re-written as

"INSERT INTO casecomments (userID, enquiryID, caseID, comment, regdate, usertype) VALUES (<'@SessionID,@EnqID,@cID,@Comment,GETDATE()>,'staff')";

and then add parameters with cmd.Parameters.AddWithValue("@SessionID", Convert.ToInt32(Session["uid"]));
etc. for other parameters.

_{Sunday, June 28, 2009 8:07 PM}

SQL Server has built-in time functions:

getdate()

CURRENT_TIMESTAMP

Tom https://mvp.support.microsoft.com/profile/Tom.Moreau

_{Sunday, June 28, 2009 8:09 PM}

And how can i use it?
Vinayak Nirvani.

_{Sunday, June 28, 2009 8:20 PM}

I got it how to use but It is not solving my problem.
I dont want to use SQL function because it will update that field everytime. I want to insert value using DateTime.Now because i dont want to change that date when i am updating that row. I have another field for modification date.

Please let me know how can i solve my problem.

Thanks..Vinayak Nirvani.

_{Sunday, June 28, 2009 10:45 PM}

If you use either oneof those with an INSERT, it will not subsequently update the vale when you update the row - unless you explicitly set that value during the UPDATE. Tom https://mvp.support.microsoft.com/profile/Tom.Moreau

_{Monday, June 29, 2009 2:43 AM}

I think you are trying to ask how to make DateTime.Now into a legit date time value.  If so, check here:

http://msdn.microsoft.com/en-us/library/ms187819.aspx

And look at the date type formats.  You should be able to format the date as either an ODBC date:  { ts '1998-05-02 01:23:56.123' } or an ISO 8601 type 'YYYY-MM-DDThh:mm:ss'  

So you would want to create/find a method of reformatting the date to one of these formats.  It is a very common problem so I would expect you can find examples on the web.

I should note that ideally you would use a parameterized query where you use the native .Now value and put it into a SQLDate type, and the parameterized query would take care of the formatting for you.  Using this sort of string for a query will open yourself up to SQL Injection issues....

http://drsql.spaces.msn.com

_{Friday, July 10, 2009 8:48 AM}

Hi Vinayak,

The error seems to be caused by the column "regdate" is a char type with specific length which is not length for storing datetime.

DateTime.Now will return a datetime such as "2009-07-10 00:00:00:000", which is required at least 23 byte length for storing.

To solve the issue, I would suggest you changing the "regdate" to be date data type.

Anyways, for security reason, Naom's suggestion is good.

Thanks,
Jin ChenJin Chen - MSFT