潇湘沐
我看到两个问题：就像 HoTTab1CH 说的：OracleParameters如果构建查询，您应该始终使用（https://en.wikipedia.org/wiki/SQL_injection）您正在为 Oracle 使用 C#-Pattern。这可能会起作用（不变文化）机器人通常不会（分钟）。// This will get you the Pattern "MM/dd/yyyy"string invariantPattern = CultureInfo.InvariantCulture.DateTimeFormat.ShortDatePattern;// This will get me in a German-Environment the Pattern "dd.MM.yyyy"string invariantPattern = CultureInfo.InvariantCulture.DateTimeFormat.ShortDatePattern;这两种模式都与 Oracle 无关。它们可能有效，但你不知道。您不得在 .Net 环境之外使用它们！允许的是这样的：OracleCommand cmd = null; // You should have this one already initialized..// Your Idea:DateTime date = new DateTime(2018, 12, 31, 23, 59, 59);string csharpPattern = "dd.MM.yyyy HH:mm:ss";string oraclePattern = "dd.mm.yyyy HH24:MI:SS";string toDateQuery = "to_date('" + date.ToString(csharpPattern) + "','" + oraclePattern + "')";string sqlQuery = "SELECT * FROM mytable t WHERE t.mydate = " + toDateQuery;cmd.CommandText = sqlQuery;var reader = cmd.ExecuteReader();// Do something...// But... Better, shorter and correcter(?)DateTime date2 = new DateTime(2018, 12, 31, 23, 59, 59);cmd.CommandText = "SELECT * FROM mytable t WHERE t.mydate = :MYDATE";cmd.Parameters.Add(new OracleParameter(":MYDATE", date2));补充说明桌子CREATE TABLE TEST( TESTDATE DATE, TESTTIMESTAMP TIMESTAMP(6), TESTTIMESTAMPTIMEZONE TIMESTAMP(6) WITH TIME ZONE)C#-应用程序DateTime d = DateTime.Now;// Let OPD.Net do the work..OracleCommand cmd = con.CreateCommand();cmd.CommandText = "INSERT INTO TEST VALUES(:TESTDATE, :TESTTIMESTAMP, :TESTTIMESTAMPTIMEZONE)";cmd.Parameters.Add(new OracleParameter("TESTDATE", d));cmd.Parameters.Add(new OracleParameter("TESTTIMESTAMP", d));cmd.Parameters.Add(new OracleParameter("TESTTIMESTAMPTIMEZONE", d));cmd.ExecuteNonQuery();// Try to manually hit the OracleTypes - and loose the milliseconds..cmd = con.CreateCommand();cmd.CommandText = "INSERT INTO TEST VALUES(:TESTDATE, :TESTTIMESTAMP, :TESTTIMESTAMPTIMEZONE)";cmd.Parameters.Add(new OracleParameter("TESTDATE", OracleDbType.Date, d, System.Data.ParameterDirection.Input));cmd.Parameters.Add(new OracleParameter("TESTTIMESTAMP", OracleDbType.Date, d, System.Data.ParameterDirection.Input));cmd.Parameters.Add(new OracleParameter("TESTTIMESTAMPTIMEZONE", OracleDbType.Date, d, System.Data.ParameterDirection.Input));cmd.ExecuteNonQuery();// Set everything correct (and redundant..)cmd = con.CreateCommand();cmd.CommandText = "INSERT INTO TEST VALUES(:TESTDATE, :TESTTIMESTAMP, :TESTTIMESTAMPTIMEZONE)";cmd.Parameters.Add(new OracleParameter("TESTDATE", OracleDbType.Date, d, System.Data.ParameterDirection.Input));cmd.Parameters.Add(new OracleParameter("TESTTIMESTAMP", OracleDbType.TimeStamp, d, System.Data.ParameterDirection.Input));cmd.Parameters.Add(new OracleParameter("TESTTIMESTAMPTIMEZONE", OracleDbType.TimeStampTZ, d, System.Data.ParameterDirection.Input));cmd.ExecuteNonQuery();数据库数据| TESTDATE | TESTTIMESTAMP | TESTTIMESTAMPTIMEZONE || 16/08/2018 11:07:23 | 16/08/2018 11:07:23,079714 | 16/08/2018 11:07:23,079714 +02:00 || 16/08/2018 11:07:23 | 16/08/2018 11:07:23,000000 | 16/08/2018 11:07:23,000000 +02:00 | | 16/08/2018 11:07:23 | 16/08/2018 11:07:23,079714 | 16/08/2018 11:07:23,079714 +02:00 |如你看到的。示例程序确实选择了错误的类型。在 cmd 中没有显式类型，ODP.Net 的工作是正确的。OPD.Net 有每个 C#-Type 到 OracleDbTypes 的映射。您不必告诉 Oracle DateTime 是什么！https://docs.oracle.com/cd/B28359_01/win.111/b28375/featTypes.htm如果您开始在 C# 代码中设置类型，则会得到双重声明。您的数据库告诉您的客户如何转换变量。如果您将 Db-Column 从 Date 更改为 Timestamp，您也必须更改您的 C#-App！如果您有多个应用程序访问您的数据库，您将有很多工作要做。在某些情况下，例如使用具有可为空类型的数组，您应该设置类型，但通常不必这样做。