SQL 判断给定日期值(或时间段)所在星期的星期一和星期天的日期

一、给定一个日期值，求出此日期所在星期的星期一和星期天的日期数据
例如给定一个日期 2010-09-01，求出它所在星期的星期一是2010-08-30，星期天是2010-09-05

function创建如下:

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测试:

select * from my_oneday_getweekfirstandendday('2010-09-01')

二、以上面单个日期搜索为基础，由用户输入两个参数，一个是开始日期，一个结束日期，根据这两个参数，求出在此时期段内的所有星期的星期一和星期天的日期表并排序。
例如开始日期是2011-09-01,结束日期是2011-10-06，我们就可以得到此星期表如下:
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创建如下:

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测试:

select * from my_range_getweekfirstandenddays('2011-09-01','2011-10-06')