oracle 向oracle查询添加日期参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15515938/
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
Add date parameter to oracle query
提问by BrOSs
What I'm trying to do is to execute the query between two dates, and the dates would come from a parameter.
我想要做的是在两个日期之间执行查询，日期将来自一个参数。
In the below code, I'm putting one parameter and letting the second date as fixed just for example purposes.
在下面的代码中，我放置了一个参数并让第二个日期固定仅用于示例目的。
The code / query is kind of long to paste it here, but here is a short version:
代码/查询在这里粘贴有点长，但这里是一个简短的版本：
OracleParameter fromDateParameter = new OracleParameter();
fromDateParameter.OracleDbType = OracleDbType.Varchar2;
fromDateParameter.Value = "'3/06/2013 20:00:00'";
this.oracleDataAdapter4.SelectCommand = new OracleCommand("
SELECT DISTINCT (LOG.RID) FROM LOG WHERE LOG.TIMESTAMP
BETWEEN TO_DATE(:fromDateParameter, 'MM/DD/YYYY hh24:mi:ss ')
AND TO_DATE('3/06/2013 23:59:00', 'MM/DD/YYYY hh24:mi:ss '))",
oracleConnection4);
oracleDataAdapter4.SelectCommand.Parameters.Add(fromDateParameter);
this.oracleDataAdapter4.Fill(event11);
And I got this message:
我收到了这条消息：
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-01858: 在需要数字的地方发现了非数字字符
I did the same query without the parameter and it works just fine.
我在没有参数的情况下做了同样的查询，它工作得很好。
Thanks
谢谢
回答by BrOSs
I didn't find a way to do it using Varchar2 Datatype. So, I'm giving the answer using Date Datatype.
我没有找到使用 Varchar2 数据类型的方法。所以，我使用日期数据类型给出答案。
if (dateTo.Minute > 30)
minToInt = 30;
else
minToInt = 00;
dateFrom = DateTime.Now;
dateTo = DateTime.Now;
DateTime dateFrom = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, dateFrom.Hour, 00, 00);
DateTime dateTo = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, dateTo.Hour, minToInt, 00);
////////FROM DATE/////////
OracleParameter fromDateParameter = new OracleParameter();
fromDateParameter.OracleDbType = OracleDbType.Date;
fromDateParameter.Value = dateFrom;
////////TO DATE/////////
OracleParameter toDateParameter = new OracleParameter();
toDateParameter.OracleDbType = OracleDbType.Date;
toDateParameter.Value = dateTo;
this.oracleDataAdapter4.SelectCommand = new OracleCommand("
SELECT DISTINCT (LOG.RID) FROM LOG WHERE LOG.TIMESTAMP
BETWEEN :fromDateParameter
AND :toDateParameter)", oracleConnection4);
oracleDataAdapter4.SelectCommand.Parameters.Add(fromDateParameter);
oracleDataAdapter4.SelectCommand.Parameters.Add(toDateParameter);
this.oracleDataAdapter4.Fill(event11);
Don't get confuse with the if/else minute validation, it is just for my query purpose.
不要与 if/else 分钟验证混淆，它仅用于我的查询目的。
