SQL determines the date of the Monday and Sunday of the week where the given date value (or time period) is located

1. Given a date value, obtain the date data of the Monday and Sunday of the week where the date is located.
For example, if you specify a date, find that its Monday of the week is, and Sunday is.

Function is created as follows:Copy codeThe Code is as follows: USE [MSSQL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create function [dbo]. [My_OneDay_GetWeekFirstAndEndDay] (@ tmpDate DATETIME)
RETURNS @ tmpTable TABLE (FirstDay DATETIME, EndDay DATETIME)
AS
BEGIN
Insert into @ tmpTable
SELECT a. FirstDay, B. EndDay FROM (
SELECT 1 as id, DATEADD (wk, DATEDIFF (wk, 0, @ tmpDate), 0) AS FirstDAy
)
Left join (
SELECT 1 as id, DATEADD (wk, DATEDIFF (wk, 0, @ tmpDate), 6) AS EndDay
) B
ON a. ID = B. ID
RETURN
End

Function test:Copy codeThe Code is as follows: SELECT * from My_OneDay_GetWeekFirstAndEndDay ('2017-09-01 ')

2. Based on a single date search, the user enters two parameters: start date and end date. Based on these two parameters, calculate and sort the date tables of all weeks of Monday and Sunday in this period.
For example, if the start date is and the end date is, we can get the table for this week as follows:
WeekOrder FirstDay EndDay
1 00:00:00. 000 00:00:00. 000
2 00:00:00. 000 00:00:00. 000
3 00:00:00. 000 00:00:00. 000
4 00:00:00. 000 00:00:00. 000
5 00:00:00. 000 2011-10-02 00:00:00. 000
6 2011-10-03 00:00:00. 000 2011-10-09 00:00:00. 000

Function is created as follows:Copy codeThe Code is as follows: USE [MSSQL]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create function [dbo]. [MY_Range_GetWeekFirstAndEndDays] (@ tmpDateSTART DATETIME, @ tmpDateEND DATETIME)
RETURNS @ tmpTable TABLE (WeekOrder INT, FirstDay DATETIME, EndDay DATETIME)
AS
BEGIN
DECLARE @ tmpDate DATETIME
DECLARE @ index INT
SET @ tmpDate = @ tmpDateSTART
SET @ index = 1
WHILE @ tmpDate <= @ tmpDateEND
BEGIN
Insert into @ tmpTable
SELECT @ index, a. FirstDay, B. EndDay FROM (
SELECT 1 as id, DATEADD (wk, DATEDIFF (wk, 0, @ tmpDate), 0) AS FirstDAy)
Left join (
SELECT 1 as id, DATEADD (wk, DATEDIFF (wk, 0, @ tmpDate), 6) AS EndDay) B
ON a. ID = B. ID

SET @ tmpDate = DATEADD (DAY, 7, @ tmpDate)
SET @ index = @ index + 1
END
RETURN
End

Function test:Copy codeThe Code is as follows: SELECT * from My_Range_GetWeekFirstAndEndDays ('2017-09-01 ', '2017-10-06 ')