当我尝试将日期和时间插入 Oracle 时得到 ORA-01843
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1567104/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
got ORA-01843 when I try to insert date & time to Oracle
提问by Gold
I have A anb B in String format
我有字符串格式的 A anb B
A = 14/01/2007
A = 14/01/2007
B = 22:10:39
乙 = 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 ?
我收到 ORA-01843 错误，我该怎么办？
thank's in advance
提前致谢
采纳答案by Danny
The error is due to the month, try:
错误是由于月份，请尝试：
TO_DATE(A, 'DD/MM/YYYY')
TO_DATE(A, 'DD/MM/YYYY')
回答by Jon Skeet
Don't use raw SQL to insert values. Use a parameterized query instead. Parse your strings into .NET DateTime(or DateTimeOffset) and TimeSpanvalues in the normal way, and then use something like:
不要使用原始 SQL 来插入值。请改用参数化查询。以正常方式将您的字符串解析为 .NET DateTime（或DateTimeOffset）和TimeSpan值，然后使用以下内容：
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.)
（显然根据您的实际字段的类型进行调整。）
回答by Cracker
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.
然而，我尝试了 Jon 方法，它对我来说也不起作用。所以我找到了这个日期时间的方法。也许这对未来的某人也有帮助。
OracleParameter oPrm;
oPrm = cmd.CreateParameter();
oPrm.ParameterName = ":myDate";
oPrm.OracleDbType = OracleDbType.Date;
oPrm.Value = DateTime.Now; //for date
cmd.Parameters.Add(oPrm);
回答by Jason Baker
Remember that Oracle doesn't have a time-only field.
请记住，Oracle 没有仅限时间的字段。
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:
您正在尝试将仅限时间的字段插入日期时间。我的猜测是 CLR 正在将 B 转换为 00/00/00 22:10:39，这不是有效的 oracle 日期。例如：
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.
无论哪种方式， Convert.ToDateTime(B) 可能都没有返回正确的东西。
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.
...但我猜这只是这里的一个错字。
