如下两段代码,为hibernate生成的,均可以在sql plus中正确执行,
但是第一段可以在hibernate正确执行,第二段代码报错,
Hibernate中ORACLE对日期格式的比较用 < <= > >=这几种情况怎么会有问题呢?相当的疑惑!
有经验的朋友给个说法!谢谢
当前ORACLE的日期格式默认为 yyyy-MM-dd HH24:mi:ss
select
to_char(talkrecord0_.CALLTIME, 'yyyy-MM-dd hh24') as col_0_0_,
count(talkrecord0_.RECORDID) as col_1_0_
from
TALKRECORD talkrecord0_
where
talkrecord0_.CALLTIME>=to_date('2010-04-20 00:00:00', 'yyyy-MM-dd HH24:mi:ss')
and talkrecord0_.CALLTIME<=to_date('2010-04-20 23:59:59', 'yyyy-MM-dd HH24:mi:ss')
and 1=1
group by
to_char(talkrecord0_.CALLTIME, 'yyyy-MM-dd hh24')
select
to_char(talkrecord0_.CALLTIME, 'yyyy-MM-dd hh24') as col_0_0_,
count(talkrecord0_.RECORDID) as col_1_0_
from TALKRECORD talkrecord0_
where
talkrecord0_.CALLTIME>='2010-04-20 00:00:00'
and talkrecord0_.CALLTIME<='2010-04-20 23:59:59'
and 1=1
group by
to_char(talkrecord0_.CALLTIME, 'yyyy-MM-dd hh24')//WARN - SQL Error: 1861, SQLState: 22008
//ERROR - ORA-01861: 文字与格式字符串不匹配
但是第一段可以在hibernate正确执行,第二段代码报错,
Hibernate中ORACLE对日期格式的比较用 < <= > >=这几种情况怎么会有问题呢?相当的疑惑!
有经验的朋友给个说法!谢谢
当前ORACLE的日期格式默认为 yyyy-MM-dd HH24:mi:ss
select
to_char(talkrecord0_.CALLTIME, 'yyyy-MM-dd hh24') as col_0_0_,
count(talkrecord0_.RECORDID) as col_1_0_
from
TALKRECORD talkrecord0_
where
talkrecord0_.CALLTIME>=to_date('2010-04-20 00:00:00', 'yyyy-MM-dd HH24:mi:ss')
and talkrecord0_.CALLTIME<=to_date('2010-04-20 23:59:59', 'yyyy-MM-dd HH24:mi:ss')
and 1=1
group by
to_char(talkrecord0_.CALLTIME, 'yyyy-MM-dd hh24')
select
to_char(talkrecord0_.CALLTIME, 'yyyy-MM-dd hh24') as col_0_0_,
count(talkrecord0_.RECORDID) as col_1_0_
from TALKRECORD talkrecord0_
where
talkrecord0_.CALLTIME>='2010-04-20 00:00:00'
and talkrecord0_.CALLTIME<='2010-04-20 23:59:59'
and 1=1
group by
to_char(talkrecord0_.CALLTIME, 'yyyy-MM-dd hh24')//WARN - SQL Error: 1861, SQLState: 22008
//ERROR - ORA-01861: 文字与格式字符串不匹配
talkrecord0_.CALLTIME<='2010-04-20 23:59:59'
这个地方写的不正确to_char(talkrecord0_.CALLTIME, 'yyyy-MM-dd hh24') as col_0_0_,
count(talkrecord0_.RECORDID) as col_1_0_
这里既然使用了as,那你后面where为什么不使用as声明的名称呢?
现在的问题是第二段代码为什么COPY到SQLPLUS中是可以正确执行,但是HIBERNATE却报错,
在ORACLE中比较日期用 < > 应该也没错啊
日期类型,还是String?
谁还去用String来表示日期的
比如有年月日的,有日月年的,有月日年的,这里肯定是默认的那种格式有问题了,所以出错了
查下错误编码ORA-01861就知道了
java.util.Date类型的话,写到SQL语句中的格式与ORACLE的默认时间格式不一致.
系统默认格式SQL> select sysdate from dual;SYSDATE
-------------------
2010-04-21 16:05:48按照这种格式,第二个查询应该不会出问题吧
关于他们能在PLSQL执行
原因是在PLSQL存在隐式转换
你的第二个语句生成的他直接是字符串
哪你传进去的也应该是字符串了
hibernate经过处理了
就知道为什么不能比较了
只是不理解 ORACLE 中日期设置成默认了 yyyy-MM-dd HH24:mi:ss
那么来说 HIBERNATE 调用的时候应该就是用默认的吧另附代码,StringBuffer hql = new StringBuffer();
CallType callType = (CallType) map.get("calltype");
ServiceType serviceType = (ServiceType) map.get("servicetype");
YesNo iflink = (YesNo) map.get("iflink");
String starttime = (String) map.get("starttime");
String endtime = (String) map.get("endtime");
String model = (String) map.get("model");
String frm = "yyyy-MM-dd hh24";
hql.append("select to_char(calltime,'").append(frm).append("') as b,count(uid) from Talkrecord where ");
if (Judge.hasLength(starttime)) {
//语句一
//hql.append(Talkrecord.CALLTIME).append(">=to_date('").append(starttime).append("', 'yyyy-MM-dd HH24:mi:ss') and ");
//语句二
hql.append(Talkrecord.CALLTIME).append(">='").append(starttime).append("' and ");
}
if (Judge.hasLength(endtime)) {
//语句一
//hql.append(Talkrecord.CALLTIME).append("<=to_date('").append(endtime).append("', 'yyyy-MM-dd HH24:mi:ss')and ");
//语句二
hql.append(Talkrecord.CALLTIME).append("<='").append(endtime).append("' and ");
}
if (!Judge.isNull(callType)) {
hql.append(Talkrecord.CALLTYPE).append("='").append(callType).append("' and ");
}
if (!Judge.isNull(serviceType)) {
hql.append(Talkrecord.SERVICETYPE).append("='").append(serviceType).append("' and ");
}
if (!Judge.isNull(iflink)) {
hql.append(Talkrecord.IFLINK).append("='").append(iflink).append("' and ");
}
hql.append("1=1");
hql.append(" group by to_char(calltime,'" + frm + "')");
Query query = this.getLocalSession().createQuery(hql.toString());
page = Execute.createExecute(page).excute(query);
你把第二局用SQL,你看会错吗
CALLTIME你配置的是什么类型呢??
应该是date,不会是字符串吧
hibernate还没有这么智能去判断一个字符串是否是日期。。
to_char(talkrecord0_.CALLTIME, 'yyyy-MM-dd hh24') as col_0_0_,
count(talkrecord0_.RECORDID) as col_1_0_
from TALKRECORD talkrecord0_
where
talkrecord0_.CALLTIME>='2010-04-20 00:00:00'
and talkrecord0_.CALLTIME<='2010-04-20 23:59:59'
and 1=1
group by
to_char(talkrecord0_.CALLTIME, 'yyyy-MM-dd hh24')
”)如果这样,应该不会错吧
吼吼,还是有错,改SQL也一样撒
SQL> select
2 to_char(talkrecord0_.CALLTIME, 'yyyy-MM-dd hh24') as col_0_0_,
3 count(talkrecord0_.RECORDID) as col_1_0_
4 from TALKRECORD talkrecord0_
5 where
6 talkrecord0_.CALLTIME>='2010-04-20 00:00:00'
7 and talkrecord0_.CALLTIME<='2010-04-20 23:59:59'
8 and 1=1
9 group by
10 to_char(talkrecord0_.CALLTIME, 'yyyy-MM-dd hh24');COL_0_0_ COL_1_0_
-------------------------- ----------
2010-04-20 11 4
2010-04-20 14 4
2010-04-20 15 3
SQL> select * from user_tables t where t.last_analyzed < '2010-05-01 00:00:00'
2 ;
select * from user_tables t where t.last_analyzed < '2010-05-01 00:00:00'
*
第 1 行出现错误:
ORA-01861: 文字与格式字符串不匹配我的不行?
兄弟我也遇到过这样的问题 时间好像用between xxx and xxx 吧 当初我是这样解决的!
System.out.println(this.getLocalSession().createSQLQuery(s).list().get(0));执行结果为
Hibernate: select sysdate from dual
2010-04-21为什么不是SQLPLUS中的默认日期格式
总而言之,现在我想知道,Hibernate中调用ORACLE数据库的时,
使用的日期格式为什么不是系统默认的格式,是不是HIBERNATE在哪里进行了设置