今天
select*from表名whereto_days(时间字段名)=to_days(now());
昨天
SELECT*FROM表名WHERETO_DAYS( NOW( ) )-TO_DAYS( 时间字段名)<=1
7天
SELECT*FROM表名whereDATE_SUB(CURDATE(), INTERVAL7DAY)<=date(时间字段名)
近30天
SELECT*FROM表名whereDATE_SUB(CURDATE(), INTERVAL30DAY)<=date(时间字段名)
本月
SELECT*FROM表名WHEREDATE_FORMAT( 时间字段名,'%Y%m')=DATE_FORMAT( CURDATE( ) ,'%Y%m')
上一月
SELECT*FROM表名WHEREPERIOD_DIFF( date_format( now( ) ,'%Y%m') , date_format( 时间字段名,'%Y%m') )=1
#查询本季度数据
select*from`ht_invoice_information`whereQUARTER(create_date)=QUARTER(now())
#查询上季度数据
select*from`ht_invoice_information`whereQUARTER(create_date)=QUARTER(DATE_SUB(now(),interval1QUARTER));
#查询本年数据
select*from`ht_invoice_information`whereYEAR(create_date)=YEAR(NOW());
#查询上年数据
select*from`ht_invoice_information`whereyear(create_date)=year(date_sub(now(),interval1year));
查询当前这周的数据
SELECTname,submittimeFROMenterpriseWHEREYEARWEEK(date_format(submittime,'%Y-%m-%d'))=YEARWEEK(now());
查询上周的数据
SELECTname,submittimeFROMenterpriseWHEREYEARWEEK(date_format(submittime,'%Y-%m-%d'))=YEARWEEK(now())-1;
查询当前月份的数据
selectname,submittimefromenterprisewheredate_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')
查询距离当前现在6个月的数据
selectname,submittimefromenterprisewheresubmittimebetweendate_sub(now(),interval6month)andnow();
