Various SQL statements for statistics by date and time period

-- Previous day Select * from table name where datediff (D, cast (date as datetime), getdate () = 1 -- Last week Select * from table name where datediff (WW, cast (date as datetime), getdate () = 1 -- Last week Select * from table name where datepart (WW, date) = datepart (WW, getdate ()-1 and datepart (YY, date) = datepart (YY, getdate ()) -- Last month select * from table name where datediff (month, cast (date as datetime), getdate () = 1 -- Last year Select * from table name where datediff (year, cast (date as datetime), getdate () = 1 -- calculate select count (*), Day ([date]) by day from table name where year ([date]) = '000000' group by day ([date]) -- by week select count (*), datename (Week, [date]) from table name where year ([date]) = '000000' group by datename (Week, [date]) -- select count (*) by month, month ([date]) from table name where year ([date]) = '000000' group by month ([date]) -- select count (*) by quarter, datename (quarter, [date]) from table name where year ([date]) = '000000' group by datename (quarter, [date]) -- by year select count (*), Year ([date]) from table name where Year ([date])> = '000000' group by year ([date])