如题:今日碰到这个问题,比较郁闷,干脆自己写了个函数
代码
--
-- 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
-- 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