如何得到一段时间内有几个星期六和星期天。

[brotherfromaq]

如何得到一段时间内有几个星期六和星期天。

[Softlee81307]

如果是一個表的字段的話
select datepart(dw,日期) from 表 where (datepart(dw,日期)=1 or datepart(dw,日期)=7)
and 日期 between '2005-01-01' and '2005-03-01'

[xjking0331]

不知道有没有现成的方法，我的想法是最笨的循环判断。

[ohui]

up:

[天地客人]

Declare @StartDate [Datetime]
Declare @EndDate [Datetime]

Declare @LoopDate [Datetime]

Declare @WeekNum [int]

Set @StartDate = '2005-3-1'
Set @EndDate = GetDate()
Set @WeekNum = 0
Set @LoopDate = @StartDate
While @LoopDate <= @EndDate
Begin
If (DATEPART(weekday, @LoopDate)=1 or DATEPART(weekday, @LoopDate)=7)
Set @WeekNum = @WeekNum + 1
Set @LoopDate = DateAdd(d,1,@LoopDate)

End

Print @WeekNum

[Softlee81307]

Create Proc ppp
@begin datetime,
@end datetime
as
declare @k datetime
create table #c (bb datetime,flag int)
set @k=@begin
while @k<=@end
begin
if datepart(dw,@k)=1 or datepart(dw,@k)=7
insert into #c values(@k,datepart(dw,@k))
set @k=dateadd(dd,1,@k)
end
select flag=(case flag when 1 then '周天' else '周六' end),count1=count(flag) from #c group by flag
drop table #c
-------------------------------------------
exec ppp '2005-03-01','2005-03-31'

[Softlee81307]

Create Proc ppp
@begin datetime,
@end datetime
as
declare @k datetime
create table #c (bb datetime,flag int)
set @k=@begin
while @k<=@end
begin
if datepart(dw,@k)=1 or datepart(dw,@k)=7
insert into #c values(@k,datepart(dw,@k))
set @k=dateadd(dd,1,@k)
end
select flag=(case flag when 1 then '周天' else '周六' end),count1=count(flag) from #c group by flag
drop table #c
-------------------------------------------
exec ppp '2005-03-01','2005-03-31'
---------------輸出---------------
周天 4
周六 4

[子陌红尘]

--创建存储过程
create procedure sp_getweekend(
@strdate varchar(10),
@enddate varchar(10))
AS
SELECT
Saturday = case when datepart(dw,@enddate)<7 then 0 else 1 end + datediff(wk,@strdate,@enddate),
Sunday = case when datepart(dw,@strdate)>1 then 0 else 1 end + datediff(wk,@strdate,@enddate)

--执行存储过程
exec sp_getweekend '2005-02-01','2005-02-28'

[ken2002]

收藏了

[paoluo]

Create Function GetSat(@BeginDate As DateTime,@EndDate As DateTime)
Returns Int As
Begin
Declare @DDate DateTime
Declare @SatCount Int
Set @SatCount=0
Set @DDate=@BeginDate
While @DDate <= @EndDate
Begin
If (Select Datepart(dw,@DDate))=7
set @SatCount=@SatCount+1
Set @DDate=DateAdd(d,1,@DDate)
End
Return @SatCount
End
GO
Select dbo.GetSat('2005-03-10','2005-03-29') As Saturday

[kinglh]

借楼上的思路修改了一下：
create Proc lh @begin datetime,
@end datetime,
@Saturday int out,
@Sunday int out
as
begin
set @Saturday = 0
set @Sunday = 0
while @begin<=@end
begin
if datepart(dw,@begin)=1
set @Saturday = @Saturday + 1
else if datepart(dw,@begin)=7
set @Sunday = @Sunday + 1
set @begin=dateadd(dd,1,@begin)
end
end

-----调用显示-----------
declare @Saturday int
declare @Sunday int
exec lh '2005-03-01',
'2005-03-31',
@Saturday out,
@Sunday out

print '周六有：'+cast(@Saturday as varchar(4))+ '天'
print '周六有：'+cast(@Sunday as varchar(4))+ '天'

[xluzhong]

Create Proc ppp
@begin datetime,
@end datetime,
@w6 int output,
@w7 int output
as
declare @bt datetime
declare @d6 int
declare @d7 int
set datefirst 7
if (datepart(weekday,@begin)<6)
set @bt=dateadd(d,7-datepart(weekday,@begin),@begin)
else
set @bt=@begin
set @d6=datediff(d,@bt,@end)
set @d7=datediff(d,@begin,@end)
set @w6=@d6/6
set @w7=@d7/7
go

declare @w6 int,@w7 int
exec ppp '2005-03-01','2005-03-31',@w6 out,@w7 out
select @w6 as '周六'
select @w7 as '周日'

drop proc ppp

[zjcxc]

--参考这个:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_weekdaycount]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_weekdaycount]
GO

/*--计算任意两个时间之间的星期几的次数(横向显示)

本方法直接判断 @@datefirst 做对应处理
不受 sp_language 及 set datefirst 的影响

--邹建 2004.08(引用请保留此信息)--*/

/*--调用示例

select * from f_weekdaycount('2004-9-01','2004-9-02')
--*/
create function f_weekdaycount(
@dt_begin datetime,
@dt_end datetime
)returns table
as
return(
select 跨周数
,周一=case a
when -1 then case when 1 between b and c then 1 else 0 end
when 0 then case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
else a+case when b<=1 then 1 else 0 end
+case when c>=1 then 1 else 0 end
end
,周二=case a
when -1 then case when 2 between b and c then 1 else 0 end
when 0 then case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
else a+case when b<=2 then 1 else 0 end
+case when c>=2 then 1 else 0 end
end
,周三=case a
when -1 then case when 3 between b and c then 1 else 0 end
when 0 then case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
else a+case when b<=3 then 1 else 0 end
+case when c>=3 then 1 else 0 end
end
,周四=case a
when -1 then case when 4 between b and c then 1 else 0 end
when 0 then case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
else a+case when b<=4 then 1 else 0 end
+case when c>=4 then 1 else 0 end
end
,周五=case a
when -1 then case when 5 between b and c then 1 else 0 end
when 0 then case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
else a+case when b<=5 then 1 else 0 end
+case when c>=5 then 1 else 0 end
end
,周六=case a
when -1 then case when 6 between b and c then 1 else 0 end
when 0 then case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
else a+case when b<=6 then 1 else 0 end
+case when c>=6 then 1 else 0 end
end
,周日=case a
when -1 then case when 0 between b and c then 1 else 0 end
when 0 then case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
else a+case when b<=0 then 1 else 0 end
+case when c>=0 then 1 else 0 end
end
from(
select 跨周数=case when @dt_begin<@dt_end
then (datediff(day,@dt_begin,@dt_end)+7)/7
else (datediff(day,@dt_end,@dt_begin)+7)/7 end
,a=case when @dt_begin<@dt_end
then datediff(week,@dt_begin,@dt_end)-1
else datediff(week,@dt_end,@dt_begin)-1 end
,b=case when @dt_begin<@dt_end
then (@@datefirst+datepart(weekday,@dt_begin)-1)%7
else (@@datefirst+datepart(weekday,@dt_end)-1)%7 end
,c=case when @dt_begin<@dt_end
then (@@datefirst+datepart(weekday,@dt_end)-1)%7
else (@@datefirst+datepart(weekday,@dt_begin)-1)%7 end)a
)
go

[openxyj]

mark

[openxyj]

MARK