SQL Server date calculation (year, quarter, month, week calculation)

Declare @ DT datetime
Set @ dt = getdate ()

Declare @ number int
Set @ number = 3

-- 1. Specify the first or last day of the year
-- The First Day is January 1, and the last day is December 31.

-- 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. Specify the first or last day of the quarter where the date is located.
-- A. The 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)
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 '31 'else' 30' end)

-- C. The last day of the quarter (direct pushAlgorithm)
Select dateadd (day,-1,
Convert (char (8 ),
Dateadd (month,
1 + datepart (quarter, @ DT) * 3-month (@ DT ),
@ DT ),
120) + '1 ')

-- 3. Specify the first or last day of the month in which the date is located.
-- A. The 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. Specify any day of the week of the date
Select dateadd (day, @ number-datepart (weekday, @ DT), @ DT)

-- 5. Specify any day of the week of the date
-- A. Sunday is the 1st day of a week.
Select dateadd (day, @ number-(datepart (weekday, @ DT) + @ DATEFIRST-1) % 7, @ DT)

-- B. Monday is the 1st day of a week.
Select dateadd (day, @ number-(datepart (weekday, @ DT) + @ DATEFIRST-2) % 7-1, @ DT)

 

Http://www.cnblogs.com/aierong