求救 .NET程序oralce怪问题
0
悬赏园豆：100
[已关闭问题]
关闭于 2009-12-28 13:12
PL/SQL里跑 SELECT * from s_job WHERE to_date( to_char( CreateDate,'yyyy-MM-dd'),'yyyy-MM-dd') between add_months(to_date('2009-12-27','yyyy-MM-dd') , -1) and to_date('2009-12-27','yyyy-MM-dd')没问题
但程序里
SELECT * from s_job WHERE to_date( to_char( CreateDate,'yyyy-MM-dd'),'yyyy-MM-dd') between add_months(to_date(:CREATEDATE,'yyyy-MM-dd') , -1) and to_date(:CREATEDATE,'yyyy-MM-dd')参数为'2009-12-27'，怎么就出现“错误描述: ORA-01861: literal does not match format string”这样的错误啊
PL/SQL里跑
SELECT * from s_job WHERE to_date( to_char( CreateDate,'yyyy-MM-dd'),'yyyy-MM-dd') between add_months(to_date('2009-12-27','yyyy-MM-dd') , -1) and to_date('2009-12-27','yyyy-MM-dd')
没问题
但程序里
SELECT * from s_job WHERE to_date( to_char( CreateDate,'yyyy-MM-dd'),'yyyy-MM-dd') between add_months(to_date(:CREATEDATE,'yyyy-MM-dd') , -1) and to_date(:CREATEDATE,'yyyy-MM-dd')
参数为'2009-12-27'，怎么就出现
“错误描述: ORA-01861: literal does not match format string”
这样的错误啊
问题补充：
我的函数是：
/// <summary>
/// 公司职位日报表_职位数量
/// </summary>
/// <param name="p_strDate"></param>
/// <returns></returns>
public DataTable getTopCoAllJobs (string coid,string p_strDate)
{
//string strSQL = "SELECT JobID,CtmName,JobName,Degree,WorkExp,WorkArea,TotalHire,JobType,FromWeb,WorkExpExcludFrom,WorkExpExcludTo,DegreeExclud,WorkAreaExclud from s_job WHERE coolpingcoid=:COOLPINGCOID and Status=:STATUS and to_date( to_char( CreateDate,'yyyy-MM-dd'),'yyyy-MM-dd') between add_months(to_date(:CREATEDATE,'yyyy-MM-dd') , -1) and to_date(:CREATEDATE,'yyyy-MM-dd') ";
string strSQL = "SELECT * from s_job WHERE to_date( to_char( CreateDate,'yyyy-MM-dd'),'yyyy-MM-dd') between add_months(to_date(:CREATEDATE,'yyyy-MM-dd') , -1) and to_date(:CREATEDATE,'yyyy-MM-dd') ";
OracleParameter[] objParameter = new OracleParameter[1];
//objParameter[0] = new OracleParameter(":COOLPINGCOID", System.Data.OracleClient.OracleType.Number, 10);
//objParameter[0].Value = coid;
//objParameter[1] = new OracleParameter(":STATUS", System.Data.OracleClient.OracleType.Number, 10);
//objParameter[1].Value = "-1";
objParameter[0] = new OracleParameter(":CREATEDATE", System.Data.OracleClient.OracleType.DateTime, 8);
objParameter[0].Value = p_strDate;
return ORLHelper.ExecuteReadTable(strSQL, objParameter);
}
所有回答(2)
0
CREATEDATE和CreateDate分别是什么数据类型？这两个必须是'yyyy-MM-dd'位数相关的，另外CREATEDATE前面怎么还有冒号？没这么用过。
0
兩個參數呢
public DataTable getTopCoAllJobs(string coid, string p_strDate)
{
string strSQL = "SELECT * from s_job WHERE to_date( to_char( CreateDate,'yyyy-MM-dd'),'yyyy-MM-dd') between add_months(to_date(:CREATEDATE,'yyyy-MM-dd') , -1) and to_date(:CREATEDATE,'yyyy-MM-dd') ";
OracleParameter[] objParameter = new OracleParameter[2];
objParameter[0] = new OracleParameter(":CREATEDATE", System.Data.OracleClient.OracleType.DateTime, 8);
objParameter[0].Value = p_strDate;
objParameter[1] = new OracleParameter(":CREATEDATE", System.Data.OracleClient.OracleType.DateTime, 8);
objParameter[1].Value = p_strDate;
return ORLHelper.ExecuteReadTable(strSQL, objParameter);
}