Date calculation methods commonly used in reports (personal summary)

--------------------------------------------

 

-- Title: the commonly used date calculation method in reports

 

-- Author: htl258 (Tony)

 

-- Date:-09-21

 

-- Reprinted please keep this information

 

--------------------------------------------

 

Declare @ DT datetime

Set @ dt = '2017-09-20'

-- 1.1 calculate the day of the month:

Select dateadd (mm, datediff (mm, 0, @ DT), 0) -- 00:00:00. 000

-- 1.2 calculate the last day of the month:

Select dateadd (mm, datediff (mm, 0, @ DT) +)-1 -- 00:00:00. 000

-- 2.1.1 calculate the first day of the week (Sunday is the first day): Two Methods

Select dateadd (wk, datediff (wk, 0, @ DT), 0)-1 -- 00:00:00. 000

Select @ DT + 1-datepart (W, @ DT + @ datefirst) -- 00:00:00. 000

-- 2.1.2 calculate the first day of the week (Monday is the first day ):

Select @ DT + 1-datepart (W, @ DT + @ datefirst-1) -- 00:00:00. 000

-- 2.2 calculate the last day of the week (based on the first day of the week)

-- 2.3.1 calculate the first business day of the week (Sunday is the first day ):

Select dateadd (wk, datediff (wk, 0, @ DT), 0) -- 00:00:00. 000

Select @ DT + 2-datepart (W, @ DT + @ datefirst) -- 00:00:00. 000

Select dateadd (DD, 1-(datepart (W, @ DT) + @ datefirst-1) % 7, @ DT) -- 00:00:00. 000

-- 2.3.2 calculate the first business day of the week (Monday is the first day ):

Select @ DT + 1-datepart (W, @ DT + @ datefirst-1) -- 00:00:00. 000

-- 2.4 calculate the last workday of the Week (based on the first workday)

-- 2.5 calculate the first Monday of the month:

Select dateadd (wk, datediff (wk, 0, dateadd (DD, 6-datepart (DD, @ DT), @ DT), 0)

-- 2009-09-07 00:00:00. 000

-- 3.1 The first day of the current year:

Select dateadd (YY, datediff (YY, 0, @ DT), 0) -- 00:00:00. 000

-- 3.2 the last day of the year:

Select dateadd (YY, datediff (YY, 0, @ DT) +)-1 -- 00:00:00. 000

-- 4.1 the first day of this quarter:

Select dateadd (QQ, datediff (QQ, 0, @ DT), 0) -- 00:00:00. 000

-- 4.2 request the last day of this quarter:

Select dateadd (QQ, datediff (QQ, 0, @ DT) +)-1 -- 00:00:00. 000

-- 5.1 calculate the first day of the semi-year:

Select dateadd (mm, datediff (mm, 0, dateadd (mm,-(month (@ DT)-1) % 6, @ DT), 0)

-- 00:00:00. 000

-- 5.2 calculate the last day of the semi-year:

Select dateadd (mm, datediff (mm, 0, dateadd (mm,-(month (@ DT)-1) % 6 + 6, @ DT), 0)-1

-- 00:00:00. 000

 

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/htl258/archive/2009/09/21/4574401.aspx