MySQL search SQL statement by date detailed

Using the Date_format method

SELECT * from ' Ler_items ' WHERE date_format (posttime, '%y-%m ') = ' 2013-03 '

Note: The date must be used ', otherwise no effect

Some other about 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)

Can get a date 7 days ago,

Use Date_sub (Curdate (), INTERVAL 7 day) to obtain the time 7 days ago, with Date_format to specify the output format.

Today

SELECT * FROM table name where To_days (Time field name) = To_days (now ());

Yesterday

SELECT * 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

SELECT * FROM table name where Date_sub (Curdate (), INTERVAL Day) <= DATE (Time field name)

Month

SELECT * FROM table name WHERE date_format (Time field name, '%y%m ') = Date_format (Curdate (), '%y%m ')

Last month

SELECT * FROM table name WHERE Period_diff (Date_format (now), '%y%m '), Date_format (Time field name, '%y%m ') =1
#查询本季度数据
SELECT * from ' Ht_invoice_information ' where quarter (create_date) =quarter (now ());
#查询上季度数据
SELECT * from ' Ht_invoice_information ' where quarter (create_date) =quarter (The Date_sub (now (), Interval 1 quarter));
#查询本年数据
SELECT * from ' Ht_invoice_information ' where year (create_date) =year (now);
#查询上年数据
SELECT * from ' ht_invoice_information ' where [create_date] =year (Date_sub (now (), interval 1));

Query current data for this 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;

Querying data for the current month
Select Name,submittime from Enterprise where Date_format (Submittime, '%y-%m ') =date_format (now (), '%y-%m ')

Query data from current 6 months
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 (), INTERVAL 1 MONTH), '%y-%m '

SELECT * from ' user ' where Date_format (pudate, '%y%m ') = Date_format (Curdate (), '%y%m ');

SELECT * from user where WeekOfYear (from_unixtime (pudate, '%y-%m-%d ')) = WeekOfYear (now ())

SELECT *
From user
where MONTH (From_unixtime (pudate, '%y-%m-%d ')) = MONTH (now ())

SELECT *
from [user]
Where year (From_unixtime (pudate, '%y-%m-%d ') = year (now ())
and MONTH (From_unixtime (pudate, '%y-%m-%d ')) = MONTH (now ())

SELECT *
from [user]
Where pudate between last day of last month
And the first day of next month

where date (regdate) = Curdate ();

SELECT * FROM Test where year (regdate) =year (now ()) and month (RegDate) =month (today ()) and Day (regdate) =day (now)

SELECT Date (c_instime), Curdate ()
From ' T_score '
WHERE 1
LIMIT 0, 30