当日期格式变量为“M/d/yy”且月份或日期为两位数时，TO_DATE() 函数会出错

Question

我的日期格式因用户的日期格式而异。当日期格式为 'M/d/yy' 时，它会给出错误，ORA-01821：日期格式无法识别。sDateFormat = CultureInfo.DateTimeFormat.ShortDatePattern;SELECT * FROM xxx WHERE yyy <= TO_DATE('" + sScheduleDate + "','"+sDateFormat+"')";//sScheduleDate ex: "11/15/18" //sDateFormat "M/d/yy"

Answer 1

我看到两个问题：

1. 就像 HoTTab1CH 说的：OracleParameters如果构建查询，您应该始终使用（https://en.wikipedia.org/wiki/SQL_injection）

2. 您正在为 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！如果您有多个应用程序访问您的数据库，您将有很多工作要做。

在某些情况下，例如使用具有可为空类型的数组，您应该设置类型，但通常不必这样做。

Answer 2

它无法识别，因为没有这样的“M”参数，这里是 TO_DATE() 的完整文档https://www.techonthenet.com/oracle/functions/to_date.php

试试这个，正确的格式掩码应该是 to_date('11/15/18', 'MM/DD/YY')

编辑：

为避免此问题，您最好在查询中使用参数。这是一个小例子：

 DateTime date = //get your date here

 string myQuery= "SELECT * FROM xxx WHERE yyy <= :pDate";

 OracleCommand oraCmd = new OracleCommand();

 oraCmd.CommandType = CommandType.Text;

 oraCmd.Connection = OracleConnectionSource; // your connection

 OracleParameter oraParam = new OracleParameter();

 oraParam = oraCmd.Parameters.Add("pDate", OracleDbType.Date, date, ParameterDirection.Input);

 oraCmd.CommandText = myQuery;

 OracleDataReader oraDataReader = oraCmd.ExecuteReader();