oracle 将 .NET DateTime 转换为 OracleDate
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7273076/
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
Cast .NET DateTime to OracleDate
提问by kittyhawk
I am trying to insert a .NET DateTime value into an Oracle DATE column using ODP.NET. Thus far, I have tried using the OracleDate() constructor to cast the .NET DateTime like so:
我正在尝试使用 ODP.NET 将 .NET DateTime 值插入到 Oracle DATE 列中。到目前为止，我已经尝试使用 OracleDate() 构造函数来转换 .NET DateTime，如下所示：
new OracleDate(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTimeNow.Hour, DateTime.Now.Minute, DateTime.Now.Second)
When I try this, it inserts the correct year, month, and day into the column but the time is always set to midnight. How do I insert the correct time along with the date?
当我尝试此操作时，它会在列中插入正确的年、月和日，但时间始终设置为午夜。如何插入正确的时间和日期？
Without parameters, the SQL looks like this (DateTime.Now is used for clarity, otherwise I'd just use SYSDATE):
如果没有参数，SQL 看起来像这样（为清楚起见使用 DateTime.Now，否则我将只使用 SYSDATE）：
"update mytable set timestamp = '" + new OracleTimeStamp(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Hour, DateTime.Now.Minute, DateTimeNow.Second) + "'"
回答by Yahia
According to thisyou should use OracleTimeStampas OracleDbTypeto achieve what you want...
根据this你应该使用OracleTimeStampasOracleDbType来实现你想要的......
OracleDateis without time portion... this behaviour started sometime ago (IIRC Oracle 8 or so) with the JDBC drivers... with the JDBC drivers there was/is some weird workaround which I don't remember it right now... I don't know if there is a workaround for ODP.NET... OracleTimeStampis the officially supported way for dates with time portions...
OracleDate没有时间部分......这种行为在前一段时间（IIRC Oracle 8左右）开始使用JDBC驱动程序......使用JDBC驱动程序有/有一些奇怪的解决方法，我现在不记得了......我不知道 ODP.NET 是否有解决方法...OracleTimeStamp是官方支持的带有时间部分的日期方式...
EDIT - after OP added SQL statement:
编辑 - 在 OP 添加 SQL 语句之后：
First of all this statement contains two problems - never name a column/table like a reserved word (i.e. timestamp)... the other problem is the lack of using a parameter which in this case won't lead to SQL injection but still is bad practice and leads (if this same statement is used multiple times) to a minimal loss of performance...
首先，这个语句包含两个问题 - 永远不要像保留字一样命名列/表（即timestamp）......另一个问题是缺少使用参数，在这种情况下不会导致 SQL 注入，但仍然很糟糕练习并导致（如果多次使用相同的语句）将性能损失降至最低......
IF you still want to use it that way THEN this will work:
如果您仍然想以这种方式使用它，那么这将起作用：
"update mytable set timestamp = TO_DATE ('" + DateTime.Now.ToString ("yyyyMMddHHmmss") + "', 'YYYYMMDDHH24MISS')";
回答by Cory Grimster
You should be using parameters for this. There's also no need to use the Oracle-specific datatypes when an equivalent .NET datatype will do. This has the advatage of making it easy to swap out ODP.NET for a different driver at a later date, as most other drivers work with the native .NET datatypes.
您应该为此使用参数。当等效的 .NET 数据类型可以使用时，也不需要使用特定于 Oracle 的数据类型。这样做的好处是可以在以后轻松地将 ODP.NET 换成不同的驱动程序，因为大多数其他驱动程序使用本机 .NET 数据类型。
using (OracleConnection conn = new OracleConnection("your connection string here")
{
string query = "update mytable set timestamp = :foo";
using (OracleCommand cmd = new OracleCommand(query, conn))
{
cmd.Parameters.Add(":foo", DateTime.Now);
cmd.ExecuteNonQuery();
}
}
