铸造.NET日期时间为OracleDate [英] Cast .NET DateTime to OracleDate

问题描述

我想插入一个.NET DateTime值将使用ODP.NET的Oracle DATE列。到目前为止，我已经使用OracleDate（）构造铸就.NET DateTime的尝试，像这样：

 新OracleDate（DateTime.Now.Year，DateTime.Now.Month，DateTime.Now.Day，DateTimeNow.Hour，DateTime.Now.Minute，DateTime.Now.Second）
 

当我尝试这一点，正确的年，月，日镶入列，但时间总是被设定为午夜。我要如何插入正确的时间与日期？

不带参数的SQL看起来像这样（DateTime.Now用于清楚，否则，我只是用SYSDATE）：

 更新mytable的设置时间戳='+新OracleTimeStamp（DateTime.Now.Year，DateTime.Now.Month，DateTime.Now.Day，DateTime.Now.Hour，日期时间。 Now.Minute，DateTimeNow.Second）+'
 

解决方案

据的这个你应该使用 OracleTimeStamp 为 OracleDbType 来实现你想要的..

OracleDate 是没有时间部分......这种行为开始前的某个时候（IIRC的Oracle 8左右）的JDBC驱动程序......与JDBC驱动程序有/是一些奇怪的解决办法，我不记得了，现在......我不知道是否有一种变通方法ODP.NET ... OracleTimeStamp 是正式随着时间的推移部分支持的方式为日期...

编辑 - OP之后加入SQL语句：

首先这句话包含两个问题 - 从来没有命名列/表像一个保留字（即时间戳）...的另一个问题是缺乏使用在这种情况下不会导致SQL注入，但仍然是一个参数是不好的做法和导线（如果此相同的语句多次使用），以性能的损失降到最低......

如果你仍然想使用这种方式，那么这将工作：

 更新mytable的设置时间戳= TO_DATE（'+ DateTime.Now.ToString（YYYYMMDDHHMMSS）+，YYYYMMDDHH24MISS'）;
 

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:

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

"update mytable set timestamp = '" + new OracleTimeStamp(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Hour, DateTime.Now.Minute, DateTimeNow.Second) + "'"

解决方案

According to this you should use OracleTimeStamp as OracleDbType to achieve what you want...

OracleDate is 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... OracleTimeStamp is the officially supported way for dates with time portions...

EDIT - after OP added SQL statement:

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

IF you still want to use it that way THEN this will work:

"update mytable set timestamp = TO_DATE ('" + DateTime.Now.ToString ("yyyyMMddHHmmss") + "', 'YYYYMMDDHH24MISS')";

这篇关于铸造.NET日期时间为OracleDate的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！