[Source] Practical SQL statement: date estimation

DECLARE @dt datetime
SET @dt=GETDATE()

DECLARE @number int
SET @number=3

--1. Specify the date of the first or last day of the year
--A. The first day of the year
SELECT Co nVERT(char(5),@dt,120)+ '1-1 '

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

--2. The first or last day of the quarter in which the specified date is located
--A. First day of the quarter
SELECT Co nVERT(datetime,
Co nVERT(char(8),
DATEADD(Mo nth,
DATEPART(Quarter,@dt)*3-Mo nth(@dt)-2,
@dt),
120)+ '1 ')

--B. Last day of the quarter (CASE judgment)
SELECT Co nVERT(datetime,
Co nVERT(char(8),
DATEADD(Mo nth,
DATEPART(Quarter,@dt)*3-Mo nth(@dt),
@dt),
120)
+CASE WHEN DATEPART(Quarter,@dt) in(1,4)
THEN  '31 'ELSE  '30 ' END)

--C. Last day of the quarter (direct push algorithm)
SELECT DATEADD(Day,-1,
Co nVERT(char(8),
DATEADD(Mo nth,
1+DATEPART(Quarter,@dt)*3-Mo nth(@dt),
@dt),
120)+ '1 ')

--3.The first or last day of the month in which the specified date is located
--A. The first day of the month
SELECT Co nVERT(datetime,Co nVERT(char(8),@dt,120)+ '1 ')

--B. The last day of the month
SELECT DATEADD(Day,-1,Co nVERT(char(8),DATEADD(Mo nth,1,@dt),120)+ '1 ')

--C. Last day of the month (easy to use wrong method)
SELECT DATEADD(Mo nth,1,DATEADD(Day,-DAY(@dt),@dt))

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

--5. Any day of the week in which the specified date is located
--A.  Sunday as the first day of the week
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)

--B.  Monday is the first day of the week
SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)