mysql查询指定日期

个人资源与分享网站：http://xiaocaoshare.com/

1.今天
SELECT * from hb_contract_in h where TO_DAYS(modify_date)=TO_DAYS(NOW());

2.昨天
SELECT * from hb_contract_in h where TO_DAYS(NOW())-TO_DAYS(modify_date)<=1;

3.近七天
SELECT * FROM hb_contract_in where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(modify_date);

4.近30天
SELECT * FROM hb_contract_in where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(modify_date);

5.本月
SELECT * FROM hb_contract_in WHERE DATE_FORMAT( modify_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' );

6.上一月
SELECT * FROM hb_contract_in WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( modify_date, '%Y%m' ) ) =1;

7.查询本季度数据
select * from hb_contract_in where QUARTER(modify_date)=QUARTER(now());

8.查询上季度数据
select * from hb_contract_in where QUARTER(modify_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

9.查询本年数据
select * from hb_contract_in where YEAR(modify_date)=YEAR(NOW());

10.查询上年数据
select * from hb_contract_in where year(modify_date)=year(date_sub(now(),interval 1 year));

11.查询当前这周的数据
SELECT * FROM hb_contract_in WHERE YEARWEEK(date_format(modify_date,'%Y-%m-%d')) = YEARWEEK(now());

12.查询上周的数据
SELECT * FROM hb_contract_in WHERE YEARWEEK(date_format(modify_date,'%Y-%m-%d')) = YEARWEEK(now())-1;

13.查询当前月份的数据
select * from hb_contract_in  where date_format(modify_date,'%Y-%m')=date_format(now(),'%Y-%m');

14.查询距离当前现在6个月的数据
select * from hb_contract_in where modify_date between date_sub(now(),interval 6 month) and now();