gt在oracle,CodeGo.net>如何在Oracle DB的SQL查询中传递DateTime参数

我需要在SQL查询WHERE子句中包含一些“ TIMESTAMP”字段：

SELECT * FROM PERSON WHERE PSN_CREATED_DATE >= :createdPrior

在我的代码中,createdPrior参数的定义方式如下

...

command.Parameters.Add(":createdPrior", Miscellaneous.convertToOracleTimeStamp(createdPrior));

...

static class Miscellaneous

{

public static OracleTimeStamp convertToOracleTimeStamp(DateTime dateTime)

{

OracleTimeStamp result = new OracleTimeStamp(dateTime);

return result;

}

}

结果,我收到以下异常

Exception: Additional information: ORA-00932: inconsistent datatypes:

expected TIMESTAMP got NUMBER

您能否告诉我,如何在SQL查询中为Oracle DB传递DateTime？

附言

我尝试过的

-我创建了具有特征的Oracle参数

OracleParameter para = new OracleParameter();

para.ParameterName = ":createdPrior";

para.Direction = System.Data.ParameterDirection.Input;

para.OracleDbType = OracleDbType.TimeStamp;

para.Value = Miscellaneous.convertToOracleTimeStamp(createdPrior);

-我刚刚通过了DateTime.而且我收到了另一个例外

-我尝试将参数转换为DATE类型(使用to_date()方法),但是在这种情况下,我似乎浪费了几分钟和几秒钟

解决方法:

做到这一点：

SELECT * FROM PERSON WHERE PSN_CREATED_DATE >= TO_TIMESTAMP(:createdPrior, 'yyyyMMddHH24missffff')

或在C#中使用TimeStamp参数,应该像这样

OracleParameter para = new OracleParameter(":createdPrior", OracleDbType.TimeStamp, ParameterDirection.Input);

para.Value = (Oracle.DataAccess.Types.OracleTimeStamp)value;

command.Parameters.Add(para);

标签：oracle,timestamp,datetime,sql,c

来源： https://codeday.me/bug/20191121/2054750.html