Got ORA-01843 when I try to insert date and time in Oracle

Question

I have anb B in String format

A = 01/14/2007

B = 22:10:39

I am trying to insert a date and time:

SQL = "insert into MyTbl(Tdate,Ttime) value ('" + Convert.ToDateTime(A) + "','" + Convert.ToDateTime(B) + "')";

I have error ORA-01843, what can I do?

Thanks in advance

Answer 1

The error is related to the month, try:

TO_DATE (A, 'DD / MM / YYYY)

Answer 2

Do not use raw SQL to insert values. Use a parameterized query instead. Split your lines into .NET DateTime(or DateTimeOffset) and TimeSpanvalues ​​in the usual 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, configure the types of your real fields.)

Answer 3

, Oracle .

datetime. , CLR B 00/00/00 22:10:39, . :

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

In any case, Convert.ToDateTime (B) will probably not return the right thing.

In addition, these are:

"insert into MyTbl(Tdate,Ttime) value ("

should be as follows:

"insert into MyTbl(Tdate,Ttime) values ("

... but I suppose it's just a typo here.

Answer 4

However, I tried the Jon method, it did not work for me on the date and time. So I found this method for datetime. Perhaps this will help someone in the next future.

OracleParameter oPrm;
oPrm = cmd.CreateParameter();
oPrm.ParameterName = ":myDate";
oPrm.OracleDbType = OracleDbType.Date;
oPrm.Value = DateTime.Now;  //for date
cmd.Parameters.Add(oPrm);