SQL Server queries today, yesterday, this week, last week, this month, last month data

DECLARE @dt datetime
SET @dt =getdate ()

DECLARE @number int
SET @number =3

--1. Specify a date the first or last day of the year
--the first day is January 1, the last day is December 31 is fixed

--a. The first day of the year
SELECT CONVERT (char (5), @dt, 120) + ' 1-1 '

--b. The last day of the year
SELECT CONVERT (char (5), @dt, 120) + ' 12-31 '

--2. The first or last day of the quarter on which the specified date is located
--a. First day of the quarter
SELECT CONVERT (datetime,
CONVERT (char (8),
DATEADD (Month,
DATEPART (Quarter, @dt) *3-month (@dt)-2,
@dt),
120) + ' 1 ')

--b. The last day of the quarter (case judgment method)
SELECT CONVERT (datetime,
CONVERT (char (8),
DATEADD (Month,
DATEPART (Quarter, @dt) *3-month (@dt),
@dt),
120)
+case when DATEPART (Quarter, @dt) in (1,4)
Then ' ELSE ' ' END ')

--c. The last day of the quarter (direct extrapolation method)
SELECT DATEADD (Day,-1,
CONVERT (char (8),
DATEADD (Month,
1+datepart (Quarter, @dt) *3-month (@dt),
@dt),
120) + ' 1 ')

--3. The first or last day of the month in which the specified date is located
--a. First day of the month
SELECT CONVERT (Datetime,convert (char (8), @dt, 120) + ' 1 ')

--b. The last day of the month
SELECT DATEADD (Day,-1,convert (char (8), DATEADD (month,1, @dt), 120) + ' 1 ')

--4. Any day of the week on which the specified date is located
SELECT DATEADD (Day, @number-datepart (Weekday, @dt), @dt)

--5. Any day of the week on which the specified date is located
--a. Sunday as the 1th day of the week
SELECT DATEADD (Day, @number-(DATEPART (Weekday, @dt) [email protected] @DATEFIRST-1)%7, @dt)

--b. Monday as the 1th day of the week
SELECT DATEADD (Day, @number-(DATEPART (Weekday, @dt) [email protected] @DATEFIRST-2)%7-1, @dt)

SQL Server queries today, yesterday, this week, last week, this month, last month data