sql语句查询当天当⽉的数据
今天
select*from表名where to_days(时间字段名)= to_days(now());
昨天
SELECT*FROM表名WHERE TO_DAYS(NOW())- TO_DAYS(时间字段名)<=1
近7天
SELECT*FROM表名where DATE_SUB(CURDATE(),INTERVAL7DAY)<=date(时间字段名)
近30天
SELECT*FROM表名where DATE_SUB(CURDATE(),INTERVAL30DAY)<=date(时间字段名)
本⽉
SELECT*FROM表名WHERE DATE_FORMAT(时间字段名,'%Y%m')= DATE_FORMAT( CURDATE(),'%Y%m')
上⼀⽉
SELECT*FROM表名WHERE PERIOD_DIFF( date_format(now(),'%Y%m'),date_format(时间字段名,'%Y%m'))=1
查询本季度数据
select*from`ht_invoice_information`where QUARTER(create_date)=QUARTER(now());
查询上季度数据
select*from`ht_invoice_information`where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval1 QUARTER));
查询本年数据
select*from`ht_invoice_information`where YEAR(create_date)=YEAR(NOW());
查询上年数据
select*from`ht_invoice_information`where year(create_date)=year(date_sub(now(),interval1year));⽚
查询当前这周的数据
SELECT name,submittime FROMenterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d'))= YEARWEEK(now());
select语句查询日期
查询上周的数据
SELECT name,submittime FROMenterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d'))= YEARWEEK(now())-1;
查询上个⽉的数据
select name,submittime fromenterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(),INTERVAL1MONTH),'%Y-%m') select*from user where DATE_FORMAT(pudate,'%Y%m')= DATE_FORMAT(CURDATE(),'%Y%m');
select*from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d'))= WEEKOFYEAR(now())
select*from user where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d'))=MONTH(now())
select*from user where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d'))=YEAR(now())and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d'))=MONTH( now())
select*from user where pudate between上⽉最后⼀天and下⽉第⼀天
查询当前⽉份的数据
select name,submittime fromenterprise  wheredate_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')查询距离当前现在6个⽉的数据
select name,submittime fromenterprise where submittime betweendate_sub(now(),interval6month)and now();