Refer to the following date Writing Method
--- Calculate the number of days for difference
Select datediff (day, '2017-01-01 ', getdate ())
-- 1. The first day of a month
Select dateadd (mm, datediff (mm, 0, getdate (), 0)
-- 2. Monday of the week
Select dateadd (wk, datediff (wk, 0, getdate (), 0)
Select dateadd (wk, datediff (wk, 0, getdate (), 6)
-- 3. The first day of the year
Select dateadd (YY, datediff (YY, 0, getdate (), 0)
-- 4. The first day of the quarter
Select dateadd (QQ, datediff (QQ, 0, getdate (), 0)
-- 5. midnight of the day
Select dateadd (DD, datediff (DD, 0, getdate (), 0)
-- 6. Last day of last month
Select dateadd (MS,-3, dateadd (mm, datediff (mm, 0, getdate (), 0 ))
-- 7. Last day of last year
Select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate (), 0 ))
-- 8. Last day of the month
Select dateadd (MS,-3, dateadd (mm, datediff (M, 0, getdate () + 1, 0 ))
-- 9. The last day of the year
Select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate () + 1, 0 ))
-- 10. the first Monday of the month
Select dateadd (wk, datediff (wk, 0, dateadd (DD, 6-datepart (day, getdate (), getdate (), 0)
-- Query the number of registrants registered this week
Select count (*) from [user] Where datediff (Week, create_day-1, getdate () = 0
-- Number of registrants last week
Select count (*) from [user] Where datediff (Week, create_day-1, getdate () = 1
-- Number of registrants this month
Select count (*) from [user] Where datediff (month, create_day, getdate () = 0
-- Number of registrants last month
Select count (*) from [user]
Where datediff (month, create_day, getdate () = 1
-- If efficiency is required, write the query in this way
-- Query the number of registrants registered this week
Select count (*) from [user] Where create_day> = dateadd (day, 2-datepart (weekday, getdate (), convert (varchar, getdate (), 112 ))
And create_day <dateadd (day, 9-datepart (weekday, getdate (), convert (varchar, getdate (), 112 ))
-- Number of registrants last week
Select count (*) from [user] Where create_day> = dateadd (day,-5-datepart (weekday, getdate (), convert (varchar, getdate (), 112 )) and create_day <dateadd (day, 2-datepart (weekday, getdate (), convert (varchar, getdate (), 112 ))
-- Number of registrants this month
Select count (*) from [user] Where create_day> = dateadd (day, 1-day (getdate (), convert (varchar, getdate (), 112 ))
And create_day <dateadd (month, 1, dateadd (day, 1-day (getdate (), convert (varchar, getdate (), 112 )))
-- Number of registrants last month
Select count (*) from [user] Where create_day> = dateadd (month,-1, dateadd (day, 1-day (getdate (), convert (varchar, getdate (), 112 )))
And create_day <dateadd (day, 1-day (getdate (), convert (varchar, getdate (), 112 ))