返回在指定日期内有几个指定的星期数

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