62,046
社区成员
发帖
与我相关
我的任务
分享
--创建函数(传入年和月份)
create function getWorkDays( @year int , @month int )
returns int as
begin
declare @CountDay int
declare @temptime datetime
set @CountDay = 0
set @temptime = convert(varchar,@year) + '-'+ convert(varchar,@month) + '-01'
while(MONTH(@temptime) = @month)
begin
if DATEPART(weekday,@temptime) <>1 and DATEPART(weekday,@temptime)<>7
begin
set @CountDay = @CountDay + 1
End
set @temptime = DATEADD(day,1,@temptime)
end
return @CountDay
end
--调用
select dbo.getWorkDays(2009,7)
/*
一个月有几个工作日取决于当月天数和1号是周几
四 五 六 日 一~三
28 20 20 20 20 20
29 21 21 20 20 21
30 22 21 20 21 22
31 22 21 21 22 23
*/
DECLARE @date AS DATETIME
SET @date='2009-7-1' --@Date为要求月份的第一天
SELECT
CASE DATEDIFF(DAY,@date,DATEADD(MONTH,1,@date))
WHEN 28 THEN 20
WHEN 29 THEN CASE DATEPART(dw,@date) % 6 WHEN 1 THEN 20 ELSE 21 END
WHEN 30 THEN CASE DATEPART(dw,@date) WHEN 6 THEN 21 WHEN 1 THEN 21 WHEN 7 THEN 20 ELSE 22 END
ELSE CASE DATEPART(dw,@date)WHEN 5 THEN 22 WHEN 6 THEN 21 WHEN 7 THEN 21 WHEN 1 THEN 22 ELSE 23 END
END
/*
-----------
23
(1 行受影响)
*/
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
declare @date datetime
set @date='2009-06-01'
select datediff(dd,@date,dateadd(mm,1,@date))-dbo.f_getnums(convert(varchar(07),@date,120)) as workpay
/*
workpay
-----------
22
*/