Calculate date functions

[Email protected]
[Email protected] = getdate ()

[Email protected]
[Email protected] = 3

-- 1. Specify the first or last day of the year
-- A. The first day of the year
Selectconvert (char (5), @ DT, 120) + '1-1'

-- B. The last day of the year
Selectconvert (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
Selectconvert (datetime,
Convert (char (8 ),
Dateadd (month,
Datepart (quarter, @ DT) * 3-month (@ DT)-2,
@ DT ),
120) + '1 ')

-- B. The last day of the quarter)
Selectconvert (datetime,
Convert (char (8 ),
Dateadd (month,
Datepart (quarter, @ DT) * 3-month (@ DT ),
@ DT ),
120)
+ Casewhendatepart (quarter, @ DT) in (1, 4)
Then '31 'else' 30' end)

-- C. The last day of the quarter (Direct Algorithm)
Selectdateadd (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
Selectconvert (datetime, convert (char (8), @ DT, 120) + '1 ')

-- B. The last day of the month
Selectdateadd (day,-1, convert (char (8), dateadd (month, 1, @ DT), 120) + '1 ')

-- C. The last day of the month (the easy-to-use error method)
Selectdateadd (month, 1, dateadd (day,-day (@ DT), @ DT ))


-- 4. Specify any day of the week of the date
Selectdateadd (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.
Selectdateadd (day, @ number-(datepart (weekday, @ DT) [email protected] @ DATEFIRST-1) % 7, @ DT)

-- B. Monday is the 1st day of a week.
Selectdateadd (day, @ number-(datepart (weekday, @ DT) [email protected] @ DATEFIRST-2) % 7-1, @ DT)