如何在 SQL 查询中为 Oracle DB 传递 DateTime 参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23214898/
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
How to pass DateTime parameter in SQL query for Oracle DB
提问by Eugene
I need to include some 'TIMESTAMP' fields in SQL-query WHERE clause:
我需要在 SQL 查询 WHERE 子句中包含一些“TIMESTAMP”字段：
SELECT * FROM PERSON WHERE PSN_CREATED_DATE >= :createdPrior
In my code, createdPriorparameter is defined in the following way
在我的代码中，createdPrior参数的定义方式如下
...
command.Parameters.Add(":createdPrior", Miscellaneous.convertToOracleTimeStamp(createdPrior));
...
static class Miscellaneous
{
public static OracleTimeStamp convertToOracleTimeStamp(DateTime dateTime)
{
OracleTimeStamp result = new OracleTimeStamp(dateTime);
return result;
}
}
And as a result I receive a following exception
结果我收到以下异常
Exception: Additional information: ORA-00932: inconsistent datatypes: expected TIMESTAMP got NUMBER
例外：附加信息：ORA-00932：不一致的数据类型：预期的 TIMESTAMP got NUMBER
Could you tell me, how to pass DateTime in SQL-query for Oracle DB?
你能告诉我，如何在 Oracle DB 的 SQL 查询中传递 DateTime 吗？
P.S. What I've tried: -I've created an Oracle Parameter with characteristics
PS 我尝试过的： - 我创建了一个具有特征的 Oracle 参数
OracleParameter para = new OracleParameter();
para.ParameterName = ":createdPrior";
para.Direction = System.Data.ParameterDirection.Input;
para.OracleDbType = OracleDbType.TimeStamp;
para.Value = Miscellaneous.convertToOracleTimeStamp(createdPrior);
-I've passed just DateTime. And I've received another exception
- 我刚刚过了 DateTime。我收到了另一个例外
-I've tried to convert parameter to DATE type (using to_date() method), but in this case it seems that I'm loosing minutes and seconds
- 我试图将参数转换为 DATE 类型（使用 to_date() 方法），但在这种情况下，我似乎失去了分秒
采纳答案by Wernfried Domscheit
Do this one:
做这个：
SELECT * FROM PERSON WHERE PSN_CREATED_DATE >= TO_TIMESTAMP(:createdPrior, 'yyyyMMddHH24missffff')
or use TimeStamp paramater in C#, should be like this
或者在 C# 中使用 TimeStamp 参数，应该是这样的
OracleParameter para = new OracleParameter(":createdPrior", OracleDbType.TimeStamp, ParameterDirection.Input);
para.Value = (Oracle.DataAccess.Types.OracleTimeStamp)value;
command.Parameters.Add(para);
回答by Florent Gz
Try using OracleParameter
尝试使用OracleParameter
OracleParameter para = new OracleParameter();
para.ParameterName = ":createdPrior";
para.Direction = ParameterDirection.Input;
para.DbType = DbType.DateTime;
para.Value = value;
command.Parameters.Add(para);
