用hibernate查询1周内过生日的员工,急

[kukoukele]

我用的插件是hibernate,数据库里有个员工表clerk,里面存储了员工的基本信息
我现在要查询所有在一周内要过生日的员工,在页面上列出来，不大懂这里面的SQL语句怎么写,clerk表里有个字段birthday,高分求助!

[ChDw]

Calendar cal = Calendar.getInstance();
cal.add(Calendar.DATE, 7);
session.createQuery("from clerk c where c.birthday < ?").setTimestamp(0, cal.getTime());

[shan1119]

birthday>=sysdate and birthday<=sysdate-7

[ChDw]

哦，算得不对。写错了

[kukoukele]

那个birthday的字段是2007-07-06

[kukoukele]

shan1119(大天使，卐～解！)
==========================
你的年份不对啊,只能查出当前年份的

[shan1119]

to_date(birthday,'YYYY-MM-DD')>=sysdate and to_date(birthday,'YYYY-MM-DD')<=sysdate-7

[ssnec]

综合2楼3楼的就是答案.

[kukoukele]

综合2楼和3楼的SQL语句在mysql里查不到结果
PS:工生日提醒（一周前开始提醒，有效期为该员工生日当天结束后）

[shan1119]

to_char(to_date(birthday,'YYYY-MM-DD'),'MM-DD')>=to_char(sysdate ,'MM-DD')
and to_char(to_date(birthday,'YYYY-MM-DD'),'MM-DD')<=to_char(sysdate-7 ,'MM-DD')

[shan1119]

我是写的oracle的.不好意思.

[kukoukele]

有在mysql里的吗?
我想应该有日期这方面的函数,就是不知道是哪些

[shan1119]

DATE_FORMAT(birthday,'%m%d')>=DATE_FORMAT(NOW() ,'%m%d')
and DATE_FORMAT(birthday,'%m%d')>=DATE_FORMAT(DATE_ADD(NOW(),Interval 7 DAY) ,'%m%d')

[shan1119]

http://www.bitscn.com/mysql/art/20060430/20213.html

没用过mysql,上边是照着网上例子写的,有可能有错,你参照上边的网站,里边有对日期的处理.

[kukoukele]

SELECT birthday FROM ehr.hr_clerk WHERE DATE_FORMAT(birthday,'%m%d')>=DATE_FORMAT(NOW() ,'%m%d')
and DATE_FORMAT(birthday,'%m%d')<=DATE_FORMAT(DATE_ADD(NOW(),Interval 7 DAY) ,'%m%d')

下面一行是<=
真强,谢谢
这个双休得把这方面加强一下

[kukoukele]

郁闷啊,这东西放到mysql里可以通过
但放到java代码里,就出错啦!
List<Clerk> birthdayClerkList = null;
try{
String hql = "from Clerk cle where DATE_FORMAT(birthday,'%m%d') >= " +
"DATE_FORMAT(NOW() ,'%m%d') and DATE_FORMAT(birthday,'%m%d')" +
" <= DATE_FORMAT(DATE_ADD(NOW(),Interval 7 DAY) ,'%m%d')";
//Query query = getSession().createQuery(hql);
//birthdayClerkList = query.list();
birthdayClerkList = this.getHibernateTemplate().find(hql);
}catch (DataAccessException e) {
log.error("Error in getAllBirthdayClerkList.", e);
throw new DaoException("Error in getAllBirthdayClerkList.", e);
}

====================================
org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: 7 near line 1, column 187 [from com.foundersoftware.ehr.object.hr.Clerk cle where DATE_FORMAT(birthday,'%m%d') >= DATE_FORMAT(NOW() ,'%m%d') and DATE_FORMAT(birthday,'%m%d') <= DATE_FORMAT(DATE_ADD(NOW(),Interval 7 DAY) ,'%m%d')]; nested exception is org.hibernate.hql.ast.QuerySyntaxError: unexpected token: 7 near line 1, column 187 [from com.foundersoftware.ehr.object.hr.Clerk cle where DATE_FORMAT(birthday,'%m%d') >= DATE_FORMAT(NOW() ,'%m%d') and DATE_FORMAT(birthday,'%m%d') <= DATE_FORMAT(DATE_ADD(NOW(),Interval 7 DAY) ,'%m%d')]
Caused by:
org.hibernate.hql.ast.QuerySyntaxError: unexpected token: 7 near line 1, column 187 [from com.foundersoftware.ehr.object.hr.Clerk cle where DATE_FORMAT(birthday,'%m%d') >= DATE_FORMAT(NOW() ,'%m%d') and DATE_FORMAT(birthday,'%m%d') <= DATE_FORMAT(DATE_ADD(NOW(),Interval 7 DAY) ,'%m%d')]
求解!

[kukoukele]

怎么结贴了就没人回答了啊