First, given a date value, find out the date data for the Monday and Sunday of the week in which this date is located
For example, given a date of 2010-09-01, find out where it is in the week of Monday is 2010-08-30, Sunday is 2010-09-05
function is created as follows:
Copy Code code 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
) A
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 Code code as follows:
SELECT * from My_oneday_getweekfirstandendday (' 2010-09-01 ')
Second, the above single date search as the basis, by the user input two parameters, one is the start date, an end date, according to these two parameters, find out in this period of all the weeks of the Monday and Sunday date table and sort.
For example, the start date is 2011-09-01, and the end date is 2011-10-06, and we can get this week table as follows:
Weekorder FirstDay Endday
1 2011-08-29 00:00:00.000 2011-09-04 00:00:00.000
2 2011-09-05 00:00:00.000 2011-09-11 00:00:00.000
3 2011-09-12 00:00:00.000 2011-09-18 00:00:00.000
4 2011-09-19 00:00:00.000 2011-09-25 00:00:00.000
5 2011-09-26 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 Code code 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) a
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 Code code as follows:
SELECT * from My_range_getweekfirstandenddays (' 2011-09-01 ', ' 2011-10-06 ')
