34,590
社区成员
发帖
与我相关
我的任务
分享
declare @date1 datetime,@date2 datetime,@i int
select @date1='2008-02-01',@date2='2008-03-01',@i=0
while @date1<=@date2
begin
if datepart(dw,@date1) not in(1,7)--默认情况下@@datefirst为7
set @i=@i+1
set @date1=dateadd(day,1,@date1)
end
select @i
-----------
21
(所影响的行数为 1 行)
create function F_month(@YMonth nvarchar(6))
returns @T table(日 varchar(4),一 varchar(4),二 varchar(4),三 varchar(4),四 varchar(4),五 varchar(4),六 varchar(4))
as
begin
declare @Tmp table([weekday] int,[day] nvarchar(2),[group] int)---增加一列作为分组显示
declare @i int,@j int,@date datetime,@group int
select @date=@YMonth+'01',@i=datediff(dd,@date,dateadd(month,1,@date)),@j=0,@group=0
while @i>@j
begin
insert @Tmp select (datepart(dw,@date)+@@datefirst-1)%7,datepart(d,@date),case when (datepart(dw,@date)+@@datefirst-1)%7=0 then @group+1 else @group end
select @j=@j+1,@group=case when (datepart(dw,@date)+@@datefirst-1)%7=0 then @group+1 else @group end,@date=dateadd(dd,1,@date)
end
insert @T
select
max(case when [weekday]=0 then [day] else '' end),
max(case when [weekday]=1 then [day] else '' end),
max(case when [weekday]=2 then [day] else '' end),
max(case when [weekday]=3 then [day] else '' end),
max(case when [weekday]=4 then [day] else '' end),
max(case when [weekday]=5 then [day] else '' end),
max(case when [weekday]=6 then [day] else '' end)
from
@Tmp
group by [group]
return
end
go
select * from F_month('0712')
或:
select * from F_month('200712')
--drop function F_month