mysql根據日期查詢

標籤：io   ar   for   on   line   sql   mysql   table   br   

select * from table where to_days(dateline) = to_days(now());
select * from table where date(dateline) = curdate();

--查詢昨天記錄
select * from table where to_days(dateline) = to_days(now())-1;
select * from table where date(dateline) = curdate()-1; --今天是本月的第一天查不到上月最後一天記錄

--查詢本周記錄
select * from table where YEARWEEK(date_format(dateline,‘%Y-%m-%d‘)) = YEARWEEK(now());
select * from table where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(dateline); --查前7天

--查詢上周記錄
select * from table where YEARWEEK(date_format(dateline,‘%Y-%m-%d‘)) = YEARWEEK(now())-1;

--查詢本月記錄
select * from table where DATE_FORMAT(dateline, ‘%Y%m‘) = DATE_FORMAT(CURDATE(),‘%Y%m‘);
select * from table where DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(dateline);--查前30天

--查詢上月記錄
select * from table where PERIOD_DIFF(date_format( now() ,‘%Y%m‘) , date_format(dateline, ‘%Y%m‘ ) ) =1;
select * from table where DATE_FORMAT(dateline, ‘%Y%m‘) = DATE_FORMAT(CURDATE(),‘%Y%m‘)-1;
select * from table where date_format(dateline,‘%Y-%m‘)=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),‘%Y-%m‘); --查前60天到前30天

mysql根據日期查詢