--输入月,返回月的周数,和相关的开始日期和结束日期
--返回本月的所有周数。
--select * from f_mtow('200603')
CREATE function f_mtow(@yd char(6))
returns @temp table(a datetime,b datetime,c int)
as
begin
declare @i int
declare @adata datetime
declare @adata1 datetime
set @i=1
set @adata=convert(datetime,@yd+'01')
set @adata1=@adata
while(month(@adata1)=month(@adata))
begin
insert into @temp select *,@i from get_firstweekday(@adata1)
set @i=@i+1
set @adata1=dateadd(week,1,@adata1)
end
set @adata1=dateadd(day,-6,@adata1)
-- insert into @temp values(@adata1,@adata1,6)
if (month(@adata1)=month(@adata))
insert into @temp select *,@i from get_firstweekday(@adata1)
--删除重复数据
delete @temp where a in(select a from @temp group by a having(count(a))>1) and c=(select max(c) from @temp)
return
end
--获取本日所在周的周日(系统第一天),如果跨月,则取本周所在月的第一天
--获取本日所在周的周六(系统最后一天)
CREATE FUNCTION get_firstweekday(@thisdate datetime)
returns @temp table(a datetime,b datetime)
AS
BEGIN
declare @ddata datetime
declare @edata datetime
declare @fdata datetime
set @fdata=convert(datetime,convert(char(4),year(@thisdate))+'-01-01')
set @ddata=dateadd(day,datepart(week,@thisdate)*7-7,@fdata)
set @edata=dateadd(day,6,@ddata)
if month(@edata)<>month(@thisdate)
set @edata=dateadd(day,-1,convert(datetime,convert(char(7),dateadd(month,1,@thisdate),120)+'-01'))
if month(@ddata)<>month(@thisdate)
set @ddata=convert(datetime,convert(char(7),@thisdate,120)+'-01')
insert into @temp values(@ddata,@edata)
RETURN
END 如果我输入的是200801
a b c
2008-01-01 00:00:00.000 2008-01-07 00:00:00.000 1
2008-01-08 00:00:00.000 2008-01-14 00:00:00.000 2
2008-01-15 00:00:00.000 2008-01-21 00:00:00.000 3
2008-01-22 00:00:00.000 2008-01-28 00:00:00.000 4
2008-01-29 00:00:00.000 2008-01-31 00:00:00.000 5而应该上是
a b c
2008-01-01 00:00:00.000 2008-01-06 00:00:00.000 1
2008-01-07 00:00:00.000 2008-01-13 00:00:00.000 2
2008-01-14 00:00:00.000 2008-01-20 00:00:00.000 3
2008-01-21 00:00:00.000 2008-01-27 00:00:00.000 4
2008-01-28 00:00:00.000 2008-01-30 00:00:00.000 5 怎么修改给函数
--返回本月的所有周数。
--select * from f_mtow('200603')
CREATE function f_mtow(@yd char(6))
returns @temp table(a datetime,b datetime,c int)
as
begin
declare @i int
declare @adata datetime
declare @adata1 datetime
set @i=1
set @adata=convert(datetime,@yd+'01')
set @adata1=@adata
while(month(@adata1)=month(@adata))
begin
insert into @temp select *,@i from get_firstweekday(@adata1)
set @i=@i+1
set @adata1=dateadd(week,1,@adata1)
end
set @adata1=dateadd(day,-6,@adata1)
-- insert into @temp values(@adata1,@adata1,6)
if (month(@adata1)=month(@adata))
insert into @temp select *,@i from get_firstweekday(@adata1)
--删除重复数据
delete @temp where a in(select a from @temp group by a having(count(a))>1) and c=(select max(c) from @temp)
return
end
--获取本日所在周的周日(系统第一天),如果跨月,则取本周所在月的第一天
--获取本日所在周的周六(系统最后一天)
CREATE FUNCTION get_firstweekday(@thisdate datetime)
returns @temp table(a datetime,b datetime)
AS
BEGIN
declare @ddata datetime
declare @edata datetime
declare @fdata datetime
set @fdata=convert(datetime,convert(char(4),year(@thisdate))+'-01-01')
set @ddata=dateadd(day,datepart(week,@thisdate)*7-7,@fdata)
set @edata=dateadd(day,6,@ddata)
if month(@edata)<>month(@thisdate)
set @edata=dateadd(day,-1,convert(datetime,convert(char(7),dateadd(month,1,@thisdate),120)+'-01'))
if month(@ddata)<>month(@thisdate)
set @ddata=convert(datetime,convert(char(7),@thisdate,120)+'-01')
insert into @temp values(@ddata,@edata)
RETURN
END 如果我输入的是200801
a b c
2008-01-01 00:00:00.000 2008-01-07 00:00:00.000 1
2008-01-08 00:00:00.000 2008-01-14 00:00:00.000 2
2008-01-15 00:00:00.000 2008-01-21 00:00:00.000 3
2008-01-22 00:00:00.000 2008-01-28 00:00:00.000 4
2008-01-29 00:00:00.000 2008-01-31 00:00:00.000 5而应该上是
a b c
2008-01-01 00:00:00.000 2008-01-06 00:00:00.000 1
2008-01-07 00:00:00.000 2008-01-13 00:00:00.000 2
2008-01-14 00:00:00.000 2008-01-20 00:00:00.000 3
2008-01-21 00:00:00.000 2008-01-27 00:00:00.000 4
2008-01-28 00:00:00.000 2008-01-30 00:00:00.000 5 怎么修改给函数
2007-12-01 -- 2007-12-02 这个就是第一周!!