A question about the time field type caused by Oracle update statements

I want to use the Oracle database to update data when I was building an ASP. NET Website. The fields in the table include int, string, and date. net front-end controls always encounter problems when binding updates. I checked a lot of things online and always looked at the ambiguity. After all, it was the first time I came into contact with Oracle. I updated my initial post (now this problem has been solved). Let's take a look:

The update statement is as follows: SQL = string. format ("Update xxt_registration R set R. name = '{0}', R. IP = '{1}', R. port = '{2}', R. time = '{3}', R. location = '{4}', R. installtime = '{5}', R. type = (select D. ID from xxt_devicetype d Where D. name = '{6}') Where R. id = '{7 }'",

This.txt name. text. trim (), this.txt IP. text. trim (), this.txt port. text. trim (), this.txt time. text. trim (), this.txt location. text. trim (), this.txt installtime. text. trim (), this. dropdownlist_tname.text.trim (), this.txt ID. text. trim ());

First, the problem is the "type" and "single quotation marks". The error is reported, for example:

This error is, ORA-01861: the text does not match the format string.

It may be because the time column in your table has the data type of date.

After you pass in a character format, the database does not know how to format the character information as a date.

The following is an example and solution of error reproduction provided by a netizen. Here I am very grateful for his enthusiastic help (the link is http://zhidao.baidu.com/question/551220541? Quesup2 & oldq = 1)

SQL> Create Table test_time (time date); the table has been created. SQL> insert into test_time values ('2017-05-17 '); insert into test_time values ('2017-05-17') * error located at row 2013: ORA-01861: the text does not match the format string. SQL> insert into test_time values ('2017-05-17 16:55:30 '); insert into test_time values ('2017-05-17 16:55:30 ') * error lies in row 1st: ORA-01861: Text and format strings do not match SQL> insert into test_time values (to_date ('2017-05-17 16:55:30 ', 'yyyy-MM-DD hh24: MI: SS '); 1 line has been created.

The above update statement is summarized as follows:

The string type can be enclosed in single quotes, such as R. name = '{0}', R. IP = '{1}', R. port = '{2}' and so on;

The Int type cannot be enclosed in single quotes in the Oracle database update statement. ID = '{7}' is not acceptable. You should remove the single quotation mark and change it to id = {7 };

The date type can be enclosed in single quotes, but it is not like R. for time = '{3}', to_date () should be applied to convert the format, such as: R. time = to_date ('{3}', 'yyyy-mm-dd '). In the Asp.net foreground control, data of the string type is input. During update, the data must be converted to the Time Type format. The updated statement is as follows:

sql = string.Format("update xxt_registration r set r.name='{0}',r.ip='{1}',r.port='{2}',r.location='{3}',r.installtime=to_date('{4}','yyyy-mm-dd'),r.type=(select d.id from xxt_devicetype d where d.name='{5}') where r.id={6}",                    this.txtname.Text.Trim(), this.txtip.Text.Trim(), this.txtport.Text.Trim(), this.txtlocation.Text.Trim(), this.Calendarinstalltime.SelectedDate.ToString("yyyy-MM-dd"), this.DropDownList_tname.Text.Trim(), this.txtid.Text.Trim());

Finally, if you still need other answers in the to_date format in Oracle, the following link address will be detailed and I believe it will help you.

Http://www.cnblogs.com/ajian/archive/2009/03/25/1421063.html