SQL statements to query today, yesterday, 7 days, 30 days of data, classic!

---restore content starts---

All data for Today: SELECT * FROM table name where DateDiff (dd,datetime Type field, GETDATE ()) =0

All data yesterday: SELECT * from table name where DateDiff (dd,datetime Type field, GETDATE ()) =1

All data within 7 days: SELECT * from table name where DateDiff (dd,datetime Type field, GETDATE ()) <=7

All data within 30 days: SELECT * from table name where DateDiff (dd,datetime Type field, GETDATE ()) <=30

All data for this month: SELECT * from table name where DateDiff (mm,datetime Type field, GETDATE ()) =0

All data for this year: SELECT * from table name where DateDiff (yy,datetime Type field, GETDATE ()) =0

Query today is the first day of the year: Select DATEPART (dayofyear,getdate ())

Query today is the day of the month: 1. Select DATEPART (dd, getDate ())

2.select Day (GetDate ())

Query Week 1st of the week is how much (note: The specified date can not be Sunday, if it is Sunday will be calculated to the next Monday go. So if it's going to be minus one day in Sunday,
SELECT DATEADD (Wk,datediff (Wk,0,getdate ()), 0)

Date yesterday: Select CONVERT (Char,dateadd (Dd,-1,getdate ()), 111)//111 is the style number, (100-114)

Query first day of the month Date: Select DATEADD (mm, DATEDIFF (Mm,0,getdate ()), 0) as FirstDay

Check the last day of the month Date: Select DateAdd (Ms,-3,dateadd (mm, DATEDIFF (M,0,getdate ()) +1, 0)) as Lastday//modify-3 values will change accordingly

How many days this month: Select DatePart (Dd,dateadd (Dd,-1,dateadd (Mm,1,cast (getdate () as varchar) + '-' +cast (Month ( GETDATE ()) as varchar) + ' -01 ') as DateTime))))

Ask for a difference of two time periods: Select DateDiff (Day, ' 2016/5/17 ', ' 2016/8/20 ') as Daysum

±n days on the specified date: select CONVERT (Char,dateadd (dd,1, ' 2016/8/20 '), 111) as Riqi//Output 2016/8/21

±n minutes on the specified date: Select DATEADD (Mi,-15,getdate ())//Query the date 15 minutes before the current time

SQL statements to query today, yesterday, 7 days, 30 days of data, classic!