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

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