如何在SQL查询中为Oracle DB传递DateTime参数 [英] How to pass DateTime parameter in SQL query for Oracle DB
问题描述
我需要在SQL查询WHERE子句中包含一些'TIMESTAMP'字段:
I need to include some 'TIMESTAMP' fields in SQL-query WHERE clause:
SELECT * FROM PERSON WHERE PSN_CREATED_DATE >= :createdPrior
在我的代码中, createdPrior 参数是通过以下方式定义的
In my code, createdPrior parameter is defined in the following way
...
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
异常:附加信息:ORA-00932:数据类型不一致: 预期的时间戳记为NUMBER
Exception: Additional information: ORA-00932: inconsistent datatypes: expected TIMESTAMP got NUMBER
您能告诉我,如何在SQL查询中为Oracle DB传递DateTime吗?
Could you tell me, how to pass DateTime in SQL-query for Oracle DB?
P.S. 我尝试过的 -我创建了具有特征的Oracle参数
P.S. What I've tried: -I've created an Oracle Parameter with characteristics
OracleParameter para = new OracleParameter();
para.ParameterName = ":createdPrior";
para.Direction = System.Data.ParameterDirection.Input;
para.OracleDbType = OracleDbType.TimeStamp;
para.Value = Miscellaneous.convertToOracleTimeStamp(createdPrior);
-我只传递了DateTime.而且我还收到了另一个例外
-I've passed just DateTime. And I've received another exception
-我尝试将参数转换为DATE类型(使用to_date()方法),但是在这种情况下,我似乎损失了几分钟和几秒钟
-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
推荐答案
执行以下操作:
SELECT * FROM PERSON WHERE PSN_CREATED_DATE >= TO_TIMESTAMP(:createdPrior, 'yyyyMMddHH24missffff')
或在C#中使用TimeStamp参数,应该是这样
or use TimeStamp paramater in C#, should be like this
OracleParameter para = new OracleParameter(":createdPrior", OracleDbType.TimeStamp, ParameterDirection.Input);
para.Value = (Oracle.DataAccess.Types.OracleTimeStamp)value;
command.Parameters.Add(para);
这篇关于如何在SQL查询中为Oracle DB传递DateTime参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!