怎么抓某年某月共有几个周六?在线结贴~~~~

[gopark]

怎么抓某年某月共有几个周六?怎么写函数?

[chuifengde]

create proc GetDateNum
@sdate smalldatetime
as
declare @edate smalldatetime

set @edate=dateadd(day,-1,dateadd(month,1,@sdate))
select top 31 id=identity(int,0,1) into # from syscolumns
select count(1) 总数 from (
select dateadd(day,id,@sdate) g,datepart(weekday,dateadd(day,id,@sdate)) a from # where dateadd(day,id,@sdate) <=@edate
)aa where a=7

drop table #
go
exec GetDateNum '2007-06-01'

[SoftwKLC]

---创建函数
Create Function Fn_Day(@Year varchar(4),@Month varchar(2))
Returns @T table(D datetime)
As
Begin
Declare @i int
Set @i=1
While @i<=31 and isdate(@Year+'-'+@Month+'-'+cast(@i as varchar))=1
Begin
Insert @T values(@Year+'-'+@Month+'-'+cast(@i as varchar))
Set @i=@i+1
End
Return
End
Go
---查询结果
Select Count(1) As 总共有周六个数
From
dbo.Fn_Day('2007','8')
Where Datepart(weekday,D)=7 --星期六为7
　　　　　　　　　　　　　　 --星期天为1　　　
/*
总共有周六个数
-----------
4

（所影响的行数为 1 行）
*/

[肥胖的柠檬]

declare @date varchar(20)
set @date='200701'
set @date=@date+'01'


select (datediff(dd,@date,dateadd(mm,1,@date))+DATEPART (dw , @date)-1)/7

[mengmou]

这个邹老大写过了。
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

[肥胖的柠檬]

Create Function Fn_Day(@date varchar(20))
RETURNS int
As
Begin
declare @wcon int
set @date=@date+'01'
select @wcon=(datediff(dd,@date,dateadd(mm,1,@date))+DATEPART (dw , @date)-1)/7
Return @wcon
End
Go

select dbo.Fn_Day('200701')

[gopark]

to ljsql(第 1 行: '脑子' 附近有语法错误。)

你的写法如果要抓有几个周三要怎么改啊?

[数字化从业者老高]

用邹老大的例子就能实现了！

[ice241018]

学习

[sp4]

ljsql的不行
chuifengde(树上的鸟儿)的可以
where a=7 这里的7换成你的星期几就可以（需要注意SQL的星期默认规则 7 是星期6 6是星期5 1是星期日）

[newqq]

学习