看你这个最近是个什么概念了。比如十天。 以下为sql语句: oracle:select id,birthday from dat_user where to_char(sysdate+10,'YYYYMMDD')>=to_char(birthday,'YYYYMMDD');postgres: select id,birthday from dat_user where to_char(now()+interval '10 days','YYYYMMDD')>=to_char(birthday,'YYYYMMDD');mysql: select id,birthday from dat_user where DATE_ADD(now(),interval 10 day)>=DATE_ADD(birthday,interval 0 day);sqlserver: select id,birthday from dat_user where dateadd(day,10,getdate()) >=birthday;db2: select id,birthday from dat_user where (current date + 10 days)>=birthday; 友情up。oracle、postgres测试过,其他机器上没装就没测。
SELECT stu_name, stu_birthday FROM student WHERE (convert(Datetime,SUBSTRING(convert(varchar,stu_birthday),1,6)+'2009') BETWEEN getdate() AND (getdate()+7))测试了一下 最近一个星期生日的人 hql不知道支不支持可以用hibernate的原生sql hibernateSession.createSQLQuery("")
oracle的。 select c.id,c.birthday from( ( select a.id id, to_char(a.birthday,'YYYYMMDD') birthday from dat_user a where to_char(a.birthday,'MMDD')>to_char(sysdate,'MMDD') ) union all ( select b.id id, to_char( b.birthday+365,'YYYYMMDD') birthday from dat_user b where to_char( b.birthday,'MMDD')<=to_char(sysdate,'MMDD') ) ) c order by birthday ; ID BIRTHDAY ----- ---------------- 1 20091130 1 20091201 2 20100125 2 20101118
SELECT * FROM tablename WHERE DATE_FORMAT(DATE_ADD(birthday,interval -10 day),'%m%d') <DATE_FORMAT(now(),'%m%d');
MySQL:最近十天SELECT * FROM tablename WHERE DATE_FORMAT(DATE_ADD(birthday,interval -10 day),'%m%d') <DATE_FORMAT(now(),'%m%d'); ORDER BY DATE_FORMAT(birthday,'%m%d');
ERROR [http-8080-3] (org.hibernate.hql.ast.ErrorCounter:33) - line 1:198: unexpected token: day
从当前天数的第二天算起过生日SELECT * FROM tablename WHERE DATE_FORMAT(DATE_ADD(now(),interval 1 day),'%m%d')<DATE_FORMAT(birthday,'%m%d') ORDER BY DATE_FORMAT(birthday,'%m%d');
select * from users order by datediff(....)搜索一下个数据库计算时间差的函数,按这个升序一下就好了
// 一天所有日期 Date dayOfMonthArray[] = DateUtils2.getMonthDays(new java.sql.Date( new Date().getTime())); // 当月最后一天 Date lastDayOfMonth = dayOfMonthArray[dayOfMonthArray.length - 1]; // 当月最后一天所在日期 int lastDay = Integer.parseInt(DateUtils2.formatUtilDate( lastDayOfMonth, "dd")); // 今年所在日期 int thisDay = Integer.parseInt(DateUtils2.formatUtilDate(new Date(), "dd")); if (thisDay + 7 <= lastDay) { queryWhere += " and extract(month from birthday)=" + Integer.parseInt(DateUtils2.formatUtilDate(new Date(), "MM")); queryWhere += " and extract(day from birthday)>= " + thisDay + " and extract(day from birthday)<=" + (thisDay + 7); } else { queryWhere += " and ("; queryWhere += " (extract(month from birthday)=" + Integer.parseInt(DateUtils2.formatUtilDate(new Date(), "MM")); queryWhere += " and extract(day from birthday)>= " + thisDay + " and extract(day from birthday)<=" + lastDay + ")"; queryWhere += " or (extract(month from birthday)=" + (Integer.parseInt(DateUtils2.formatUtilDate(new Date(), "MM")) + 1); queryWhere += " and extract(day from birthday)<=" + (7 - (lastDay - thisDay)) + ")"; queryWhere += " )"; } this.log.info("查询条件:" + queryWhere); // 查询员工信息 String queryHql = " from " + this.getClassName(EmployeeInfo.class) + " where 1=1 and isValidate=:isValidate and isDelete=:isDelete and userStatus=:userStatus " + queryWhere + " order by birthday asc"; Query query = this.getSession().createQuery(queryHql); query.setInteger("isValidate", new Integer(EmployeeInfo.IS_VALIDATE)); query.setInteger("isDelete", new Integer(EmployeeInfo.NO_DELETED)); query.setInteger("userStatus", new Integer( EmployeeInfo.EMPLOYEE_STATUS_ZS)); return query.list();
from MTOA.T_EMPLOYEE_INFO employeein0_ where 1=1 and employeein0_.IS_VALIDATE=? and employeein0_.IS_DELETE=? and employeein0_.USER_STATUS=? and extract(month from employeein0_.BIRTHDAY)=9 and extract(day from employeein0_.BIRTHDAY)>=17 and extract(day from employeein0_.BIRTHDAY)<=24 order by employeein0_.BIRTHDAY asc
以下为sql语句:
oracle:select id,birthday from dat_user where to_char(sysdate+10,'YYYYMMDD')>=to_char(birthday,'YYYYMMDD');postgres:
select id,birthday from dat_user where to_char(now()+interval '10 days','YYYYMMDD')>=to_char(birthday,'YYYYMMDD');mysql:
select id,birthday from dat_user where DATE_ADD(now(),interval 10 day)>=DATE_ADD(birthday,interval 0 day);sqlserver:
select id,birthday from dat_user where dateadd(day,10,getdate()) >=birthday;db2:
select id,birthday from dat_user where (current date + 10 days)>=birthday;
友情up。oracle、postgres测试过,其他机器上没装就没测。
FROM student
WHERE (convert(Datetime,SUBSTRING(convert(varchar,stu_birthday),1,6)+'2009') BETWEEN getdate() AND (getdate()+7))测试了一下 最近一个星期生日的人
hql不知道支不支持可以用hibernate的原生sql
hibernateSession.createSQLQuery("")
2楼好快啊我上面给的是sqlserver2005的
原来是sql问题。
楼主 说的时间段 是指的什么。
把需求说清楚啊
这样大家好帮你解决
那你直接按birthday 排序不就完啦?
birthday的月、日大于当前日期的月、日,则年不加1;月日小于当前日期的月、日就年+1,然后再排。其实你隐含了一个时间段(一年),否则不好排了。比如明天过生日的,是否明年的明天也得排进去?
select c.id,c.birthday from(
(
select a.id id,
to_char(a.birthday,'YYYYMMDD') birthday
from dat_user a
where to_char(a.birthday,'MMDD')>to_char(sysdate,'MMDD')
)
union all
(
select b.id id,
to_char( b.birthday+365,'YYYYMMDD') birthday
from dat_user b
where to_char( b.birthday,'MMDD')<=to_char(sysdate,'MMDD')
)
) c order by birthday ; ID BIRTHDAY
----- ----------------
1 20091130
1 20091201
2 20100125
2 20101118
SELECT * FROM tablename
WHERE DATE_FORMAT(DATE_ADD(birthday,interval -10 day),'%m%d')
<DATE_FORMAT(now(),'%m%d');
WHERE DATE_FORMAT(DATE_ADD(birthday,interval -10 day),'%m%d')
<DATE_FORMAT(now(),'%m%d');
ORDER BY DATE_FORMAT(birthday,'%m%d');
WHERE DATE_FORMAT(DATE_ADD(birthday,interval -10 day),'%m%d')
这个条件改成从当前天数的第二天算起 不要求计算10天内的数据, 或者是查找出当前年份过生日的 该怎么整
WHERE
DATE_FORMAT(DATE_ADD(now(),interval 1 day),'%m%d')<DATE_FORMAT(birthday,'%m%d')
ORDER BY DATE_FORMAT(birthday,'%m%d');
Date dayOfMonthArray[] = DateUtils2.getMonthDays(new java.sql.Date(
new Date().getTime()));
// 当月最后一天
Date lastDayOfMonth = dayOfMonthArray[dayOfMonthArray.length - 1];
// 当月最后一天所在日期
int lastDay = Integer.parseInt(DateUtils2.formatUtilDate(
lastDayOfMonth, "dd"));
// 今年所在日期
int thisDay = Integer.parseInt(DateUtils2.formatUtilDate(new Date(),
"dd"));
if (thisDay + 7 <= lastDay) {
queryWhere += " and extract(month from birthday)="
+ Integer.parseInt(DateUtils2.formatUtilDate(new Date(),
"MM"));
queryWhere += " and extract(day from birthday)>= " + thisDay
+ " and extract(day from birthday)<=" + (thisDay + 7);
} else {
queryWhere += " and (";
queryWhere += " (extract(month from birthday)="
+ Integer.parseInt(DateUtils2.formatUtilDate(new Date(),
"MM"));
queryWhere += " and extract(day from birthday)>= " + thisDay
+ " and extract(day from birthday)<=" + lastDay + ")";
queryWhere += " or (extract(month from birthday)="
+ (Integer.parseInt(DateUtils2.formatUtilDate(new Date(),
"MM")) + 1);
queryWhere += " and extract(day from birthday)<="
+ (7 - (lastDay - thisDay)) + ")";
queryWhere += " )";
}
this.log.info("查询条件:" + queryWhere);
// 查询员工信息
String queryHql = " from "
+ this.getClassName(EmployeeInfo.class)
+ " where 1=1 and isValidate=:isValidate and isDelete=:isDelete and userStatus=:userStatus "
+ queryWhere + " order by birthday asc";
Query query = this.getSession().createQuery(queryHql);
query.setInteger("isValidate", new Integer(EmployeeInfo.IS_VALIDATE));
query.setInteger("isDelete", new Integer(EmployeeInfo.NO_DELETED));
query.setInteger("userStatus", new Integer(
EmployeeInfo.EMPLOYEE_STATUS_ZS));
return query.list();
MTOA.T_EMPLOYEE_INFO employeein0_
where
1=1
and employeein0_.IS_VALIDATE=?
and employeein0_.IS_DELETE=?
and employeein0_.USER_STATUS=?
and extract(month
from
employeein0_.BIRTHDAY)=9
and extract(day
from
employeein0_.BIRTHDAY)>=17
and extract(day
from
employeein0_.BIRTHDAY)<=24
order by
employeein0_.BIRTHDAY asc