got ORA-01843 when I try to insert date & time to Oracle
I have A anb 开发者_开发知识库B in String format
A = 14/01/2007
B = 22:10:39
I try to insert date and time:
SQL = "insert into MyTbl(Tdate,Ttime) value ('" + Convert.ToDateTime(A) + "','" + Convert.ToDateTime(B) + "')";
i got ORA-01843 error, what I can do ?
thank's in advance
Don't use raw SQL to insert values. Use a parameterized query instead. Parse your strings into .NET DateTime
(or DateTimeOffset
) and TimeSpan
values in the normal way, and then use something like:
string sql = "insert into MyTbl(Tdate,Ttime) values (:date, :time)";
using (OracleCommand cmd = new OracleCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("date", OracleType.DateTime).Value = date;
cmd.Parameters.Add("time", OracleType.IntervalDayToSecond).Value = time;
cmd.ExecuteNonQuery();
}
(Obviously adjust for the types of your actual fields.)
The error is due to the month, try:
TO_DATE(A, 'DD/MM/YYYY')
Remember that Oracle doesn't have a time-only field.
You're trying to insert a time-only field into a datetime. My guess is that the CLR is turning B into 00/00/00 22:10:39, which isn't a valid oracle date. For example:
SQL> select to_date('00/00/00', 'MM/DD/YY') from dual;
select to_date('00/00/00', 'MM/DD/YY') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
Either way, Convert.ToDateTime(B) probably isn't returning the right thing.
Also, this:
"insert into MyTbl(Tdate,Ttime) value ("
should be this:
"insert into MyTbl(Tdate,Ttime) values ("
...but I'm guessing that's just a typo here.
However i tried Jon method, it didnt work for me for date also time. So i found this method for datetime. Maybe that helps someone in next future too.
OracleParameter oPrm;
oPrm = cmd.CreateParameter();
oPrm.ParameterName = ":myDate";
oPrm.OracleDbType = OracleDbType.Date;
oPrm.Value = DateTime.Now; //for date
cmd.Parameters.Add(oPrm);
精彩评论