10、MySQL根据日期查询数据的sql语句

# 查询在某段日期之间的数据:
select * from 数据表  where 时间字段名 BETWEEN '2016-02-01' AND '2016-02-05'

# 查询往前3个月的数据:
select * from 数据表 where 时间字段名 BETWEEN DATE_SUB(NOW(),INTERVAL 3 MONTH) AND NOW()

# 查询往前一年的数据:
select * from 数据表  where 时间字段名 BETWEEN DATE_SUB(NOW(),INTERVAL 1 YEAR) AND NOW()

# 查询本月的数据:
select * from 数据表 where DATE_FORMAT(时间字段名,'%Y-%m')=DATE_FORMAT(NOW(),'%Y-%m')

# 查询上月的数据:
select * from 数据表 where DATE_FORMAT(时间字段名,'%Y-%m')=DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m')

# 查询本周的数据:
select * from 数据表 where YEARWEEK(DATE_FORMAT(时间字段名,'%Y-%m-%d')) = YEARWEEK(NOW())

# 查询上周数据:
select * from 数据表 where YEARWEEK(DATE_FORMAT(CREATE_TIME,'%Y-%m-%d')) = YEARWEEK(NOW())-1

# 查询往前7天的数据:
select * from 数据表 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= 你要判断的时间字段名

# 查询往前30天的数据:
select * from 数据表 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= 你要判断的时间字段名

# 根据国人习惯 周一为一周的第一天习惯算一周的数据
SELECT * FROM t_browse_statistics WHERE YEARWEEK(date_format(brows_date,'%Y-%m-%d'),1) = YEARWEEK(now())+1
ORDER BY brows_date DESC




复制代码
 1 -- 查询本周数据
 2 SELECT * FROM t_user WHERE YEARWEEK(date_format(add_time,'%Y-%m-%d')) = YEARWEEK(now());
 3 -- 查询当前月份的数据
 4 select * from t_user where date_format(add_time,'%Y-%m')=date_format(now(),'%Y-%m')
 5 -- 查询当天数据
 6 select * from t_user where TO_DAYS(add_time) = TO_DAYS(NOW())
 7 -- 查询距离当前现在6个月的数据
 8 select * from t_user where add_time between date_sub(now(),interval 6 month) and now();
 9 -- 查询三天的数据
10 select * from t_user where DATEDIFF(deadline_time,NOW())<3 AND DATEDIFF(deadline_time,NOW())>=0



#　删除指定日期范围
DELETE FROM 表名 WHERE CreateTime>='2017-01-01 00:00:00' AND CreateTime<='2017-02-01 00:00:00'