1、查询年龄段
小于20
//查询小于20岁的人的个数,DATE_FORMAT(birthday, '%Y')其中的birthday就是数据库的生日这个date类型字段
String hql20="SELECT id, birthday,DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birthday, '00-%m-%d')) AS age from hrd_staff where status=? and company=? group by id having age< ?";
List<Object[]> list20=staffService.findBySql(hql20, status,company,20);
//25
String hql30="SELECT id, birthday,DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birthday, '00-%m-%d')) AS age from hrd_staff where status=? and company=? group by id having age >=? and age<?";
List<Object[]> list30=staffService.findBySql(hql30, status,company,20,25);
2、查询星座
( DATE_FORMAT(birthday,'%m-%d') 其中的birthday就是数据库中的生日字段
// #摩羯座
String moxie = "select id,birthday from hrd_staff where ( ( DATE_FORMAT(birthday,'%m-%d')>'12-22' and DATE_FORMAT(birthday,'%m-%d')<'12-31') or ( DATE_FORMAT(birthday,'%m-%d') >'01-01' and DATE_FORMAT(birthday,'%m-%d') <'01-19' )) and status=? and company=?";
// # 射手座
String sheshou = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'11-23' and DATE_FORMAT(birthday,'%m-%d')<'12-21' and status=? and company=?";
// #天蝎座
String tianxie = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'10-24' and DATE_FORMAT(birthday,'%m-%d')<'11-22' and status=? and company=?";
// #天秤座
String tianchegn = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'09-23' and DATE_FORMAT(birthday,'%m-%d')<'10-23' and status=? and company=?";
// #处女座
String chunv = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'08-23' and DATE_FORMAT(birthday,'%m-%d')<'09-22' and status=? and company=?";
// #狮子座
String shizi = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'07-23' and DATE_FORMAT(birthday,'%m-%d')<'08-22' and status=? and company=?";
// #巨蟹座
String juxie = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'06-22' and DATE_FORMAT(birthday,'%m-%d')<'07-22' and status=? and company=?";
// #双子座
String shuangzi = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'05-21' and DATE_FORMAT(birthday,'%m-%d')<'06-21' and status=? and company=?";
// #金牛座
String jinniu = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'04-20' and DATE_FORMAT(birthday,'%m-%d')<'05-20' and status=? and company=?";
// #白羊座
String baiyang = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'03-21' and DATE_FORMAT(birthday,'%m-%d')<'04-19' and status=? and company=?";
// #双鱼座
String shaugnyu = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'02-19' and DATE_FORMAT(birthday,'%m-%d')<'03-20' and status=? and company=?";
// #水瓶
String shuipin = "select id,birthday from hrd_staff where DATE_FORMAT(birthday,'%m-%d') >'01-20' and DATE_FORMAT(birthday,'%m-%d')<'02-18' and status=? and company=?";
3、最近7天生日的sql
int bwt=7;
//现在日期
String nowdate=DateUtil.getNow();
// 截取月日
String wnowdate=nowdate.substring(5, 10);
//获取7天后的日期
String after=DateUtil.getAfterDay(new Date(), bwt);
//截取月日
String wafter=after.substring(5, 10);
//截取月 判断是否是跨年,如果跨年则月份有可能会小于之后几天的 就比如 12.28 七天后就是01.04 这样就是月份很小了
String ny=wnowdate.substring(0,2);
String ay=wafter.substring(0,2);
int iny=Integer.valueOf(ny);
int iay=Integer.valueOf(ay);
//跨年查询
String y12="12-31";
String y1="01-01";
// 跨年查询,因为是生日,之和月份和日期有关和年没关系
if(iny>iay){
//跨年查询sql
hql+=" and ((DATE_FORMAT( birthday , '%m-%d') > '"+wnowdate+"' and DATE_FORMAT( birthday , '%m-%d') <= '"+y12+"') or (DATE_FORMAT( birthday , '%m-%d') >= '"+y1+"' and DATE_FORMAT( birthday , '%m-%d') < '"+wafter+"'))";
}else{
//正常查询sql
hql+=" and DATE_FORMAT( birthday , '%m-%d') > '"+wnowdate+"' and DATE_FORMAT( birthday , '%m-%d') < '"+wafter+"'";
}
4、获取两个日期间的天数
/**
* 两个时间参数,月 日之间的天数
* @param min
* @param max
* @return
*/
public static int days(Date min,Date max){
SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd");
//获取日期
String s1=sdf.format(min);
String s2=sdf.format(max);
//截取月份
String mm1=s1.substring(5, 7);
String mm2=s2.substring(5, 7);
//截取日期
String dd1=s1.substring(8, 10);
String dd2=s2.substring(8, 10);
//月份
int intmm1=Integer.valueOf(mm1);
int intmm2=Integer.valueOf(mm2);
//日期
int intdd1=Integer.valueOf(dd1);
int intdd2=Integer.valueOf(dd2);
int days=0;
// 吧下面的 DAY_OF_YEAR 改为 DAY_OF_MONTH 因为每年的日期不同所以不同年的同一个月的时间不一样
if(intmm1<intmm2){
Calendar aCalendar = Calendar.getInstance();
aCalendar.setTime(min);
int day1 = aCalendar.get(Calendar.DAY_OF_YEAR);
aCalendar.setTime(max);
int day2 = aCalendar.get(Calendar.DAY_OF_YEAR);
days= day2-day1;
}else if(intmm1==intmm2){
if(intdd2>intdd1){
Calendar aCalendar = Calendar.getInstance();
aCalendar.setTime(min);
int day1 = aCalendar.get(Calendar.DAY_OF_MONTH);
aCalendar.setTime(max);
int day2 = aCalendar.get(Calendar.DAY_OF_MONTH);
days= day2-day1;
}else{
Calendar aCalendar = Calendar.getInstance();
aCalendar.setTime(max);
int day1 = aCalendar.get(Calendar.DAY_OF_MONTH);
aCalendar.setTime(min);
int day2 = aCalendar.get(Calendar.DAY_OF_MONTH);
days= day2-day1;
}
}else{
Calendar aCalendar = Calendar.getInstance();
aCalendar.setTime(max);
int day1 = aCalendar.get(Calendar.DAY_OF_YEAR);
aCalendar.setTime(min);
int day2 = aCalendar.get(Calendar.DAY_OF_YEAR);
days= day2-day1;
}
//System.out.println("s1:"+s1 +" s2:"+s2 +" days:"+days);
return days;
}
这个时间就只能这样笨办法写了在这里也看到了一篇文章:https://my.oschina.net/u/1454838/blog/750356