MySql date query details

Use the DATE_FORMAT method
SELECT * FROM `ler_items` WHERE DATE_FORMAT(postTime,'%Y-%m')='2013-03'
Note: Date 1 must use "", otherwise it will not work
1 other mysql date lookup statements
mysql > select date_format(DATE_SUB(CURDATE(), INTERVAL 7 DAY),'%y%m%d');
+ -- -- -- -- -- -- -- -- --, � +
| date_format(DATE_SUB(CURDATE(), INTERVAL 7 DAY),'%y%m%d') |
+ -- -- -- -- -- -- -- -- --, � +
| 120416 |
+ -- -- -- -- -- -- -- -- --, � +
1 row in set (0.00 sec)

You can get dates from 7 days ago,
Use DATE_SUB(CURDATE(), INTERVAL 7 DAY) to get the 7 days before and date_format to specify the output format.

Today,
select * from Table name where to_days(time field name) = to_days(now());
Yesterday,
FROM * FROM Table name WHERE TO_DAYS(NOW())?? TO_DAYS(time field name) < = 1
7 days
SELECT * FROM Table name where DATE_SUB(CURDATE(), INTERVAL 7 DAY) < = date(time field name)
Nearly 30 days
Table name where DATE_SUB(CURDATE(), INTERVAL 30 DAY) < = date(time field name)
This month,
Table name WHERE DATE_FORMAT(time field name, '%Y%m') = DATE_FORMAT(CURDATE(),' %Y%m')
On January
SELECT * FROM Table name WHERE PERIOD_DIFF(date_format(now(), '%Y%m'), date_format(time field name,' %Y%m')) =1
# Query the quarterly data
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
# Query last quarter's data
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
# Query the current year data
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
# Query last year's data
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));

Query the data for the current week
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
Query last week's data
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;
Query the data for the current month
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')
Query data 6 months from the current date
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
Query last month's data
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')
select * user 'where' DATE_FORMAT(pudate, '%Y%m') = DATE_FORMAT(), '%Y%m');
select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())
select *
from user
where MONTH (FROM_UNIXTIME(pudate, '% es162EN-% m')) = MONTH (now())
select *
from [ user ]
where YEAR (FROM_UNIXTIME(pudate, '% y-% ES178en-% d')) = YEAR (now())
and MONTH (FROM_UNIXTIME(pudate, '% y-% m')) = MONTH (now())
select *
from [ user ]
The last day of last month
Day 1 of the next month
where date(regdate) = curdate();
select * from test where year(regdate)=year(now()) and month(regdate)=month(now()) and day(regdate)=day(now())
SELECT date( c_instime ) ,curdate( )
FROM `t_score`
WHERE 1
LIMIT 0 , 30