首页 > 代码库 > MySQL关于日期的查询sql
MySQL关于日期的查询sql
---查询今天的记录
SELECT * FROM wmacc.`cash_trade_record` WHERE TO_DAYS(create_date) = TO_DAYS(NOW());
SELECT * FROM wmacc.`cash_trade_record` WHERE DATE_FORMAT(create_date, ‘%Y%m%d‘) = DATE_FORMAT(CURDATE(), ‘%Y%m%d‘);
---查询昨天的记录
SELECT * FROM wmacc.`cash_trade_record` WHERE TO_DAYS(create_date) = TO_DAYS(NOW()) - 1;
---查询最近7天的记录
SELECT * FROM wmacc.`cash_trade_record` WHERE DATE_SUB(NOW(), INTERVAL 7 DAY) <= create_date;
---查询本周的记录
SELECT * FROM wmacc.`cash_trade_record` WHERE YEARWEEK(DATE_FORMAT(create_date,‘%Y%m%d‘)) = YEARWEEK(NOW());
---查询上周的记录
SELECT * FROM wmacc.`cash_trade_record` WHERE YEARWEEK(DATE_FORMAT(create_date,‘%Y%m%d‘)) = YEARWEEK(NOW()) - 1;
---查询本月的记录
SELECT * FROM wmacc.`cash_trade_record` WHERE DATE_FORMAT(create_date, ‘%Y%m‘) = DATE_FORMAT(CURDATE(), ‘%Y%m‘);
---查询上个月的记录
SELECT * FROM wmacc.`cash_trade_record` WHERE PERIOD_DIFF(DATE_FORMAT(NOW(), ‘%Y%m‘),DATE_FORMAT(create_date, ‘%Y%m‘))=1;
SELECT * FROM wmacc.`cash_trade_record` WHERE DATE_FORMAT(create_date, ‘%Y%m‘) = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), ‘%Y%m‘);
---查询本季度的记录
SELECT * FROM wmacc.`cash_trade_record` WHERE QUARTER(create_date) = QUARTER(CURDATE());
---查询上季度的记录
SELECT * FROM wmacc.`cash_trade_record` WHERE QUARTER(create_date) = QUARTER(DATE_SUB(NOW(), INTERVAL 1 QUARTER));
---查询本年的记录
SELECT * FROM wmacc.`cash_trade_record` WHERE YEAR(create_date) = YEAR(NOW());
---查询上一年的记录
SELECT * FROM wmacc.`cash_trade_record` WHERE YEAR(create_date)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));
本文出自 “Flyfish” 博客,请务必保留此出处http://9381188.blog.51cto.com/9371188/1856961
MySQL关于日期的查询sql