请问如何用写sql函数，得到某个月有多少个星期六和星期天?

[edgethinking]

如题.

[dawugui]

---创建函数
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 行）
*/

[dawugui]

---创建函数
GO
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 
总共有周六个数 = (Select Count(1) As 总共有周六个数 From dbo.Fn_Day('2008','4') Where Datepart(weekday,D)=7) , --星期六为7
总共有周日个数 = (Select Count(1) As 总共有周日个数 From dbo.Fn_Day('2008','4') Where Datepart(weekday,D)=1)   --星期日为1


drop function dbo.Fn_Day

/*
总共有周六个数     总共有周日个数     
----------- ----------- 
4           4

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

[liangCK]

2005可以用with递归

[青锋-SS]

create function f_getnums(@year_month varchar(8))
returns int
as
begin
declare @bdt datetime,@edt datetime,@i int
set @i=0
set @bdt=cast(@year_month+'-01' as datetime)
set @edt=dateadd(d,-1,dateadd(month,1,cast(@year_month+'-01' as datetime)))
while datediff(d,@bdt,@edt)>=0
begin
if datepart(dw,@bdt)=1 or datepart(dw,@bdt)=7
begin
set @i=@i+1
end
set @bdt=dateadd(d,1,@bdt)
end
return @i
end

[青锋-SS]

select dbo.f_getnums('2008-02')
--------
8

[林g]

学习!

[tim_spac]

create function fn_weekdaysofmonth(@mn char(6))
returns int as 
begin

declare @dt datetime set @dt = @mn+'01'
declare @weekday int set @weekday = 0
while @dt < dateadd(month,1,@mn+'01') begin
	if datepart(weekday,@dt) in (1,7)
		set @weekday = @weekday+1
	set @dt = @dt + 1
end
return @weekday
end
go
select dbo.fn_weekdaysofmonth('200803')
select dbo.fn_weekdaysofmonth('200804')
go
drop function dbo.fn_weekdaysofmonth
go

[Realue]

当月周日天数：
declare @mm datetime
set @mm=getdate()
select (DateDiff(day, @mm, DateAdd(month, 1, @mm))+(5+datepart(weekday,@mm))%7)/7
当月周六天数:
declare @YYMMDD1 smalldatetime,@YYMMDD2 smalldatetime
set @YYMMDD1='2008-01-01'
set @YYMMDD2=dateadd(day,-1,dateadd(month,1,@YYMMDD1))
SELECT (Cast(DatePart(dd,@YYMMDD2) as smallint)+cast(datepart(dw,@YYMMDD1) as smallint)-1)/7

[dc1728]

DECLARE @MyTime DATETIME
SELECT @MyTime = '2008-04-19'
IF DATEPART(DW,@MyTime) = 7 OR DATEPART(DW,@MyTime) = 1
BEGIN 
SELECT 1
END


-----------
1

(1 row(s) affected)

自己照着写吧.

[gahade]

select top 31 id=identity(int,0,1) into #t from sysobjects

declare @dt datetime
set @dt='2008-05-01'
select sum(case when datepart(weekday,dateadd(day,id,@dt))=7 then 1 else 0 end) as '周六天数',
	sum(case when datepart(weekday,dateadd(day,id,@dt))=1 then 1 else 0 end) as '周天天数'
from #t where datediff(month,dateadd(day,id,@dt),@dt)=0
/*
周六天数     周天天数        
----------- ----------- 
5           4

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

你也可以把datefirst的问题考虑进去

[lff642]

一群强人!