--测试:
create table HCSZ_REST_DATE(ymd int)
insert into tb_Test
select 20060101 union all
select 20060107 union all
select 20060108 union all
select 20060114 union all
select 20060115 union all
select 20060121 union all
select 20060122 union all
select 20060128 union all
select 20060129 union all
select 20060130 union all
select 20060131 union all
select 20060201 union all
select 20060202 union all
select 20060203 union all
select 20060204 union all
select 20060205 union all
select 20060211 union all
select 20060212 union all
select 20060218 union all
select 20060219 union all
select 20060226 union all
select 20060304
--创建自定义函数
CREATE function dbo.GetEndDateSubtractStartDate (@startDate varchar(8),@endDate varchar(8))
returns int
as
begin
declare @intFlag int
set @intFlag=1
if(@startDate>@endDate)
begin
declare @temp varchar(8)
set @temp=@startDate
set @startDate=@endDate
set @endDate=@temp
set @intFlag=-1
end
declare @re int
if @startDate=@endDate or (
(select count(*)from HCSZ_REST_DATE where ymd=cast(@startDate as int) or ymd=cast(@endDate as int))=2
and (select datediff(d,@startDate,@endDate))=(select count(*)from HCSZ_REST_DATE where ymd>cast(@startDate as int) and ymd<cast(@endDate as int))+1
)
set @re=0
else
begin
while(select count(*)from HCSZ_REST_DATE where ymd=cast(@startDate as int))>0
set @startDate=convert(char(8),dateadd(d,1,@startDate),112)
while(select count(*)from HCSZ_REST_DATE where ymd=cast(@endDate as int))>0
set @endDate=convert(char(8),dateadd(d,-1,@endDate),112)
set @re=datediff(d,@startDate,@endDate)-(select count(*)from HCSZ_REST_DATE where ymd>cast(@startDate as int)and ymd<cast(@endDate as int))
end
return @re*@intFlag
end
我自己编了个函数,因为这个问题对于生产型企业是很常用的,所以还请各位高手帮忙看看:
CREATE function dbo.GetEndDateSubtractStartDate (@startDate varchar(8),@endDate varchar(8))
returns int
as
begin
declare @intFlag int
set @intFlag=1
if(@startDate>@endDate)
begin
declare @temp varchar(8)
set @temp=@startDate
set @startDate=@endDate
set @endDate=@temp
set @intFlag=-1
end
declare @re int
if @startDate=@endDate or (
(select count(*)from HCSZ_REST_DATE where ymd=cast(@startDate as int) or ymd=cast(@endDate as int))=2
and (select datediff(d,@startDate,@endDate))=(select count(*)from HCSZ_REST_DATE where ymd>cast(@startDate as int) and ymd<cast(@endDate as int))+1
)
set @re=0
else
begin
while(select count(*)from HCSZ_REST_DATE where ymd=cast(@startDate as int))>0
set @startDate=convert(char(8),dateadd(d,1,@startDate),112)
while(select count(*)from HCSZ_REST_DATE where ymd=cast(@endDate as int))>0
set @endDate=convert(char(8),dateadd(d,-1,@endDate),112)
set @re=datediff(d,@startDate,@endDate)-(select count(*)from HCSZ_REST_DATE where ymd>cast(@startDate as int)and ymd<cast(@endDate as int))
end
return @re*@intFlag
end