MySQL query today, yesterday, last month SQL statements

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) &lt = 17 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 (curd  ATE (), INTERVAL Day) <= Date (Time field name) this 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# query this quarter data SELECT * from ' ht_invoice_information ' where QUARTER (create_date) =quarter (now ()), #查询上季度数据select * from ' Ht_invoice_ Information ' where QUARTER (create_date) =quarter (Date_sub (now (), Interval 1 QUARTER)): #查询本年数据select * from ' Ht_invoice_ Information ' where year (create_date) =year (now ()), #查询上年数据select * from ' ht_invoice_information ' where year (create_date ) =year (Date_sub (now (), interval 1)); Querying data for the current week SELECT Name,submittime from Enterprise WHERE yearweek (Date_format (submittime, '%y-%m-%d ')) = Yearweek (now ()); Query last weekData 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 the data from the current 6-month 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 * 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 ()) a select * from [user] where year (From_unixtime (Pudate, '%y-%m-%d ') = year (now ()) and Month (From_unixtime (pudate, '%y-%m-%d ') = month (today ()) select * FROM [user] where pudate is Tween last day of the month and next month first day where date (regdate) =  Curdate (); select * FROM Test where year (regdate) =year (now ()) and month (RegDate) =month (today ()) and day (R Egdate) =day (now ()) SELECT date (c_instime), curdate () from ' T_score ' WHERE 1LIMIT 0, 30

　　

MSSQL gets yesterday, this week, this month ...

Original link Address: http://blog.knowsky.com/186111.htm Special instructions under: The following statistics this week data, Sunday is as the first day of next week, not the last day of the week, so you put Sunday as the last day of the week when you need to getdate () On the basis of minus one day, such as DateAdd (' Days ',-1, getDate ()) This week: SELECT * FROM table where DATEDIFF (Week,c_calltime,getdate ()) =0--c_calltime For date field this month: SELECT * FROM table where DATEDIFF (Month,c_calltime,getdate ()) =0--c_calltime for date field this season: SELECT * from table where da Tediff (Qq,c_calltime,getdate ()) =0 first half year 1-6, after half year 7-12:select * from table where DATEPART (mm,c_calltime)/7 = datepart (mm, GETDATE ())/7 Yesterday select CONVERT (varchar), GETDATE ()-1,120) tomorrow select CONVERT (varchar (), GETDATE () + 1,120) last seven days select * From TB where Time field >= convert (varchar (10), GETDATE ()-7,120) followed by seven days select * from TB where Time field <= convert (varchar) , GETDATE () + 7,120) and Time field >= Time field convert and DATEADD functions are used together. With DateDiff (Day, Time column, GETDATE ()) last month select * from TB where month (time field) = Month (getdate ())-1 month SELECT * from TB where month (Time field ) = months (getdate ()) next month select * from TB where month (time field) = Month (getdate ()) + If you are looking in the table-yesterday select * FROM TabLename where DateDiff (DD, Datetimcol, GetDate ()) = Tomorrow select * from TableName Where DateDiff (DD, GetDate (), Datetimcol) = Last seven days select * from TableName where DateDiff (DD, Datetimcol, GetDate ()) <= 7--then seven days select * from TableName Where Date  Diff (DD, GetDate (), Datetimcol) <= 7--last week select * from TableName Where DateDiff (wk, Datetimcol, GetDate ()) = the Week Select * FROM TableName where DateDiff (wk, Datetimcol, GetDate ()) = 0--next week select * from TableName Where DateDiff (wk, GetDate (), D Atetimcol) = Last month select * from TableName where DateDiff (mm, Datetimcol, GetDate ()) = The month of the select * from TableName where DateDiff (mm, Datetimcol, GetDate ()) = 0--next month select * from TableName Where DateDiff (mm, GetDate (), datetimcol) = 1--------- -----------------------------------------------this week select * from TB where DATEDIFF (week, Time field, GETDATE ()) = 0 Last week select * Fro M TB where DATEDIFF (week, Time field, GETDATE ()) = 1 next week select * from TB where DATEDIFF (week, Time field, GETDATE ()) = 1------------- -------------------------------------------1. Now I need to get just the date part, time part not, SQL how to write? Select CONVERT (varchar), GETDATE (), 120) 2. The following date sql: Yesterday select CONVERT (varchar), GETDATE ()-1,120) tomorrow select Convert (varchar), GETDATE () + 1,120) Last seven days select * from TB where Time field >= convert (varchar (), GETDATE ()-7,120) followed by seven days select * FR Om TB where Time field <= convert (varchar (ten), GETDATE () + 7,120) and Time field >= Time field

　　

MySQL query today, yesterday, last month SQL statements