MySQL關於日期的查詢sql

標籤：mysql 日期查詢

---查詢今天的記錄

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