got ORA-01843 when I try to insert date & time to Oracle

Question

hi

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

Answer 1

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.)

Answer 2

Convert the Strings within C# into a DateTime format and then use the ToString()-Method of that Object.

DateTime aDat = DateTime.ParseExact("14/01/2007", "dd/MM/yyyy", Null);
DateTime bDat = DateTime.ParseExact("22:10:39", "HH:mm:ss", Null);
SQL = "insert into MyTbl(Tdate,Ttime) value ('" + aDat.ToString("put the Orcale preferred DateFormat here") + "','" + aDat.ToString("put the Orcale preferred TimeFormat here") + "')";

The problem is that cyou're doing there an implicit conversion, were Orcale ends up thinking that 14 is the month.

Or use parameterized queries.

Bobby

Answer 3

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.

Answer 4

The error is due to the month, try:

TO_DATE(A, 'DD/MM/YYYY')