跪求一个表值函数

[Grn0210]

求一个表值函数，通过一个日期，可以返回这个日期所在周的7天日期，
例：比如传入‘2015-05-16’，我要得到
2015-05-11
2015-05-12
。。。
2015-05-17

[习惯性蹭分]

if exists(select * from sys.objects where name='fn_getWeek' and type='tf')
drop function fn_getweek
go
create function fn_getWeek(@date smalldatetime)
returns @tab table(redate smalldatetime)
as begin
 declare @i int
 set @i=-6
 while @i<=6
 begin
 insert into @tab(redate)
 select dateadd(d,@i,startDate)
 from(select @date as startDate ) t
 where datediff(week,@date,dateadd(d,@i,startDate))=0
 set @i=@i+1
 end
return 
end

--select * from dbo.fn_getWeek(getdate())


   

[RINK_1]

create function fn_getWeek(@date smalldatetime) returns @tab table(redate smalldatetime) as begin INSERT INTO @tab SELECT DATEADD(DD,NUMBER-(DATEPART(WEEKDAY,@date)-1),@date) FROM MASTER.DBO.spt_values A, (SELECT DATEPART(WEEKDAY,@date)-1 AS TEST_DATE) AS B WHERE TYPE='P' AND NUMBER>=1 AND NUMBER<=7 return end

[江南小鱼]

-- =============================================
/*
	select * from FUN_GetDays(GETDATE())
*/
-- =============================================
CREATE FUNCTION [dbo].[FUN_GetDays]
(	
	-- Add the parameters for the function here
	@Day DateTime
)
RETURNS @RetTable TABLE(Item varchar(10))
AS
BEGIN
	DECLARE @NowDay VARCHAR(2)
		,@bDay INT,@nDay INT,@i INT
	
	
	set @NowDay = substring(datename(weekday,@Day),3,1)
	if @NowDay = '一'
		select @bDay = 0,@nDay = 7
	else if @NowDay = '二'
		select @bDay = 1,@nDay = 6
	else if @NowDay = '三'
		select @bDay = 2,@nDay = 5
	else if @NowDay = '四'
		select @bDay = 3,@nDay = 4
	else if @NowDay = '五'
		select @bDay = 4,@nDay = 3
	else if @NowDay = '六'
		select @bDay = 5,@nDay = 2
	else if @NowDay = '七'
		select @bDay = 6,@nDay = 1
	
	--当天
	INSERT INTO @RetTable VALUES(CONVERT(VARCHAR(10),@Day,120))
	set @i = 1
	--后面的日期
	while @i < @nDay
	begin
		INSERT INTO @RetTable SELECT CONVERT(VARCHAR(10),dateadd(day,@i,@Day),120)
		set @i = @i + 1
	end
	
	set @i = 1
	--前面的日期
	while @i <= @bDay
	begin
		INSERT INTO @RetTable SELECT CONVERT(VARCHAR(10),dateadd(day,-@i,@Day),120)
		set @i = @i + 1
	end
	
	return
END