create function my_func (@yyyymm varchar(7)) returns int as begindeclare @cnt as intselect @cnt = count(1) from ( select dateadd(dd,num,@yyyymm+'-01') dt from (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a where dateadd(dd,num,@yyyymm+'-01') <= dateadd(mm,1,@yyyymm+'-01') - 1 and datepart(weekday,dateadd(dd,num,@yyyymm+'-01')) = 7 ) tRETURN(@cnt) end goselect dbo.my_func('2010-12') /*
create function getstday ( @ym varchar(7) )returns int as begin declare @stdaypm intset @stdaypm=(datediff(d,@ym+'-01',dateadd(mm,1,convert(datetime,@ym+'-01')))-(@@datefirst-datepart(dw,@ym+'-01')+7)%7-1)/7+1 return @stdaypm end go select dbo.getstday('2010-01') union all select dbo.getstday('2010-02') union all select dbo.getstday('2010-03') union all select dbo.getstday('2010-04') union all select dbo.getstday('2010-05') union all select dbo.getstday('2010-06') union all select dbo.getstday('2010-07') union all select dbo.getstday('2010-08') union all select dbo.getstday('2010-09') union all select dbo.getstday('2010-10') union all select dbo.getstday('2010-11') union all select dbo.getstday('2010-12') go drop function dbo.getstday /* ----------- 5 4 4 4 5 4 5 4 4 5 4 4(12 行受影响)*/
上面的函数与系统的 set datefirst 设置无关.
大乌龟的函数,当 set datefirst 不为 7 的时候会出错.
create procedure usp_month ( @year int, @month int ) as begin declare @n int ,@date datetime,@ts int set @date=CONVERT(datetime,CAST(@year as varchar(4))+ (case len(@month)when 1 then '0'else '' end)+ cast(@month as varchar(2))+'01',112) set @n=0 set @ts=0 while DATEPART(MONTH,dateadd(day,@n,@date))=DATEPART(MONTH,@date) begin if DATEPART(WEEKDAY,dateadd(day,@n,@date))=7 set @ts=@ts+1 set @n=@n+1 end select @ts endexec usp_month 2010,7
CREATE FUNCTION uf_con_saturday(@year INT,@month INT) RETURNS INT BEGIN DECLARE @b_t DATETIME,@e_t DATETIME,@c INT SET @b_t = rtrim(@year)+'-'+rtrim(@month)+'-01' SET @e_t = dateadd(month,1,@b_t)-1 SELECT @c=count(*) FROM master..spt_values s WHERE s.type='p' AND dateadd(day,s.number,@b_t) <=@e_t AND datepart(dw,dateadd(day,s.number,@b_t)) = 7 RETURN @c END -- DECLARE @year INT,@month INT SET @year = 2010 SET @month = 12 SELECT dbo.uf_con_saturday(@year,@month)
as
begindeclare @cnt as intselect @cnt = count(1) from
(
select
dateadd(dd,num,@yyyymm+'-01') dt
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
dateadd(dd,num,@yyyymm+'-01') <= dateadd(mm,1,@yyyymm+'-01') - 1 and datepart(weekday,dateadd(dd,num,@yyyymm+'-01')) = 7
) tRETURN(@cnt)
end
goselect dbo.my_func('2010-12')
/*
-----------
4(所影响的行数为 1 行)
*/select dbo.my_func('2010-10')
/*
-----------
5(所影响的行数为 1 行)
*/select dbo.my_func('2011-01')
/*
-----------
5(所影响的行数为 1 行)
*/
drop function my_func
(
@ym varchar(7)
)returns int
as
begin
declare @stdaypm intset @stdaypm=(datediff(d,@ym+'-01',dateadd(mm,1,convert(datetime,@ym+'-01')))-(@@datefirst-datepart(dw,@ym+'-01')+7)%7-1)/7+1
return @stdaypm
end
go
select dbo.getstday('2010-01')
union all
select dbo.getstday('2010-02')
union all
select dbo.getstday('2010-03')
union all
select dbo.getstday('2010-04')
union all
select dbo.getstday('2010-05')
union all
select dbo.getstday('2010-06')
union all
select dbo.getstday('2010-07')
union all
select dbo.getstday('2010-08')
union all
select dbo.getstday('2010-09')
union all
select dbo.getstday('2010-10')
union all
select dbo.getstday('2010-11')
union all
select dbo.getstday('2010-12')
go
drop function dbo.getstday
/*
-----------
5
4
4
4
5
4
5
4
4
5
4
4(12 行受影响)*/
create procedure usp_month
(
@year int,
@month int
)
as
begin
declare @n int ,@date datetime,@ts int
set @date=CONVERT(datetime,CAST(@year as varchar(4))+
(case len(@month)when 1 then '0'else '' end)+
cast(@month as varchar(2))+'01',112)
set @n=0
set @ts=0
while DATEPART(MONTH,dateadd(day,@n,@date))=DATEPART(MONTH,@date)
begin
if DATEPART(WEEKDAY,dateadd(day,@n,@date))=7
set @ts=@ts+1
set @n=@n+1
end
select @ts
endexec usp_month 2010,7
RETURNS INT
BEGIN
DECLARE @b_t DATETIME,@e_t DATETIME,@c INT
SET @b_t = rtrim(@year)+'-'+rtrim(@month)+'-01'
SET @e_t = dateadd(month,1,@b_t)-1
SELECT @c=count(*)
FROM master..spt_values s
WHERE s.type='p'
AND dateadd(day,s.number,@b_t) <=@e_t
AND datepart(dw,dateadd(day,s.number,@b_t)) = 7
RETURN @c
END
--
DECLARE @year INT,@month INT
SET @year = 2010
SET @month = 12 SELECT dbo.uf_con_saturday(@year,@month)