GO
if exists (select null from sysobjects where [name] = 'fn_get_weekday_num')
drop function fn_get_weekday_num
GO
/*===================================================================================================
功能: 返回在指定日期内有几个指定的星期数
( 如: 计算出在 2006-07-01 --> 2006-07-31 之间有几个 星期日 )
参数说明:
@strat_date 日期范围的开始日期
@end_date 日期范围的结束日期
@weekday 星期数 (范围:1-7 )
1 = 星期一
2 = 星期二
3 = 星期三
4 = 星期四
5 = 星期五
6 = 星期六
7 = 星期日
例:
select dbo.fn_get_weekday_num ('2006-07-01','2006-07-31',7)
===================================================================================================*/
create function dbo.fn_get_weekday_num(@strat_date datetime ,@end_date datetime , @weekday int)
returns int
as
begin
------------------------------------------------------------------------------------------------
/*定义变量*/
declare @return_value int
declare @start_weekday int ,@end_weekday int
------------------------------------------------------------------------------------------------
/*根据系统 DATEFIRST 计算出星期*/
set @weekday = case when @weekday >= @@datefirst then @weekday- @@datefirst+1
else @weekday + 8 - @@datefirst end
/*计算出开始日期与结束日期分别为星期几*/
set @start_weekday = datepart(weekday,@strat_date)
set @end_weekday = datepart(weekday,@end_date)
------------------------------------------------------------------------------------------------
/*计算时间范围内有几个指定的星期数*/
select @return_value= datediff(day , @strat_date,@end_date)/7
+case when @start_weekday > @end_weekday
then case when (@weekday <= 7 and @weekday >= @start_weekday)
or ( @weekday >= 1 and @weekday <= @end_weekday)
then 1 else 0 end
else case when ( @weekday >= @start_weekday
and @weekday <= @end_weekday)
then 1 else 0 end
end
------------------------------------------------------------------------------------------------
/*返回值*/
return @return_value
end
GO