sqlserver：某年份某月份 是否在某时间段内的函数

 

如题：今日碰到这个问题，比较郁闷，干脆自己写了个函数

 

 

代码

--  
--  IF OBJECT_ID (N'f_dtbj') IS NOT NULL
--  DROP FUNCTION f_dtbj
--  GO

create   function  f_dtbj( @year   int , @month   int , @dt1   datetime , @dt2   datetime )
returns    int  
as  
begin

    declare   @dt   DateTime
    declare   @dtDay   int
    set   @dt = convert ( datetime , convert ( varchar , @year ) + ' - ' + convert ( varchar , @month ) + ' -1 ' )

    select   @dtDay = day ( dateadd (dd, - 1 , cast ( convert ( varchar ( 6 ), dateadd (mm, 1 , @dt ), 112 ) + ' 01 '   as   datetime ))) 
    

    declare   @inx   int
    declare   @k   int
    declare   @x1   int
    declare   @x2   int
    set   @k = 1

    while ( @k <= @dtDay )
       begin
       set   @dt = convert ( datetime , convert ( varchar , @year ) + ' - ' + convert ( varchar , @month ) + ' - ' + convert ( varchar , @k ))
       select   @x1 = datediff ( month , @dt1 , @dt )
       select   @x2 = datediff ( month , @dt , @dt2 )
       if   @x1 >= 0   and   @x2 >= 0   -- 在时间段里
           begin
           set   @k = @dtDay + 1   -- 跳出
           set   @inx =   1
           end
       else
           begin
           set   @k = @k + 1
           set   @inx = 0
           end
       end

    return   @inx
end

 

 

转载于:https://www.cnblogs.com/kingeric/archive/2010/10/02/1841151.html