MySQL query sql about dates

---query today's records

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 ');

---query yesterday's record

SELECT * from WMACC. ' Cash_trade_record ' WHERE to_days (create_date) = To_days (now ())-1;

---track records for the last 7 days

SELECT * from WMACC. ' Cash_trade_record ' WHERE date_sub (now (), INTERVAL 7 day) <= Create_date;

---query this week's records

SELECT * from WMACC. ' Cash_trade_record ' WHERE yearweek (date_format (create_date, '%y%m%d ')) = Yearweek (now ());

---Query last week's records

SELECT * from WMACC. ' Cash_trade_record ' WHERE yearweek (date_format (create_date, '%y%m%d ')) = Yearweek (now ())-1;

---query this month's records

SELECT * from WMACC. ' Cash_trade_record ' WHERE date_format (create_date, '%y%m ') = Date_format (Curdate (), '%y%m ');

---Query last month's records

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 m onth), '%y%m ');

---Check the records for this quarter

SELECT * from WMACC. ' Cash_trade_record ' WHERE QUARTER (create_date) = QUARTER (Curdate ());

---Query last quarter's records

SELECT * from WMACC. ' Cash_trade_record ' WHERE QUARTER (create_date) = QUARTER (Date_sub (now (), INTERVAL 1 QUARTER));

---Check records for this year

SELECT * from WMACC. ' Cash_trade_record ' WHERE year (create_date) = year (now ());

---Querying records for previous year

SELECT * from WMACC. ' Cash_trade_record ' WHERE year (create_date) =year (Date_sub (now (), INTERVAL 1 year));

This article is from the "flyfish" blog, make sure to keep this source http://9381188.blog.51cto.com/9371188/1856961

MySQL query sql about dates