T-sql: Three generic custom functions associated with date, secondary by week (Sunday is the last day of the week)

Function
/*
Every function has only one sentence!
In fact, from another blog I dug out:

T-SQL generates two new true Gregorian calendars
Http://blog.csdn.net/playyuer/archive/2004/04/07/2860.aspx


T-SQL generates a simple Gregorian calendar T-SQL contains the date of the month and the Year of the week
Http://blog.csdn.net/playyuer/archive/2004/04/05/2859.aspx

Due to the use of (@ @datefirst + datepart (weekday, @date))% 7 judgment Week
Therefore, it is independent of datefirst and can be adapted to various language versions of SQL Server

*/

-Sunday counts as the last day of the week (previous)

Create function Udf_weekofyear (@date datetime)
--@date Week is the first week of the year
-Sunday counts as the last day of the week (previous)
--For weekly summary group BY, no cross year data, or group by year
--group by year (date), month (date), dbo.udf_weekofyear (date), Dbo.udf_weekofmonth (date)
returns int
As
Begin
Return
(Select DateDiff (week
, case when (@ @datefirst + datepart (weekday,dateadd (Day,0,datediff day,0,dateadd (Year,datediff (year,0, @date), 0))) % 7 = 1
Then DateAdd (Day,-1,dateadd (Day,0,dateadd (Year,datediff (year,0, @date), 0)))
else DateAdd (Day,0,datediff (Day,0,dateadd (Year,datediff (year,0, @date), 0))--date The first day of the year: January 1
End
, case when (@ @datefirst + datepart (weekday, @date))% 7 = 1
Then DateAdd (day,-1, @date)
else @date
End
) + 1)
End

Go

Create function Udf_weekofmonth (@date datetime)
--Ask @date where week is the first week of the month
-Sunday counts as the last day of the week (previous)
--For weekly summary group BY, do not have a cross-month cross year data, or group by Year,month
--group by year (date), month (date), dbo.udf_weekofyear (date), Dbo.udf_weekofmonth (date)
returns int
As
Begin
Return
(Select DateDiff (week
, case when (@ @datefirst + datepart (weekday,dateadd (Month,datediff (month,0, @date), 0))% 7 = 1
Then DateAdd (Month,datediff (month,0, @date), 0)-1
else DateAdd (Month,datediff (month,0, @date), 0)
End
, case when (@ @datefirst + datepart (weekday, @date))% 7 = 1
Then @date-1
else @date
End
) + 1)
End

Go

Create function Udf_weekday (@ int, @date datetime)
returns datetime
As
Begin
/*
-Sunday counts as the last day of the week (previous)
When @ <= 1 represents mapping @date to Monday on the week
When @ = 2 represents the mapping of the @date to the week of Tuesday
When @ = 3 represents the mapping of the @date to Wednesday on the same week
When @ = 4 represents the mapping of the @date to the week of Thursday
When @ = 5 represents the @date map to the Friday of the week on which the
When @ = 6 represents the @date map to the Saturday of the week on which the
When @ >= 7 represents a map of the @date to Sunday on the week
Can be used to summarize Group by weekly, which supports cross year data across months
*/

Return
(select-@date, Datename (Weekday, @date), (@ @datefirst + datepart (weekday, @date))% 7,3-(@ @datefirst + DATEPART (weekday , @date))% 7,
DATEADD (Day
, case when (@ @datefirst + datepart (weekday, @date))% 7 = 0--Saturday
Then
Case @ between 1 and 6
Then @-6
Else 1
End
When (@ @datefirst + datepart (weekday, @date))% 7 = 1--Sunday (vii)
Then
Case @ between 1 and 6
Then @-7
else 0
End

When (@ @datefirst + datepart (weekday, @date))% 7 between 2 and 6-Monday to Friday
Then
Case @ between 1 and 6
Then @ + 1-(@ @datefirst + datepart (weekday, @date))% 7
Else 8-(@ @datefirst + datepart (weekday, @date))% 7
End
End
, @date))
/*
Test:

Select Date,datename (weekday,date), ' Map to: ', Dbo.udf_weekday (2,date), Datename (Weekday,dbo.udf_weekday (1,date))
From T
ORDER BY date

--===============
Set Datefirst 4
DECLARE @ int,@a int
SET @ = 1


Select Date,datename (weekday,date), (@ @datefirst + datepart (weekday,date))% 7,3-(@ @datefirst + datepart (weekday,date) )% 7,
DATEADD (Day
, case when (@ @datefirst + datepart (weekday,date))% 7 = 0--Saturday
Then
Case @ between 2 and 7
Then-(7-@)
else @
End
When (@ @datefirst + datepart (weekday,date))% 7 = 1--Sunday
Then
Case @ between 2 and 7
Then-(7-@)-1
else @-1
End

When (@ @datefirst + datepart (weekday,date))% 7 between 2 and 6
Then
Case @ between 2 and 7
Then @-(@ @datefirst + datepart (weekday,date))% 7
Else 8-(@ @datefirst + datepart (weekday,date))% 7
End


End
, date)
From D
ORDER BY date
*/

End