declare @sDate datetime,@eDate datetime
set ...
select
case when (@sDate between Date1 and Date2) or (@eDate between Date1 and Date2)
then datediff(dd,
when @sDate<=Date1 then Date1 else @sDate end
when @sDate>=Date2 then Date2 else @eDate end)+1
when @sDate<Date1 and @eDate>Date1
then datediff(dd,Date1,Date2)+1
else 0
end
from
表
SELECT re = SUM(DATEDIFF(Day,
CASE WHEN Date1 < @dt1 THEN @dt1 ELSE Date1 END,
CASE WHEN Date2 < @dt2 THEN Date2 ELSE @dt2 END
) + 1)
FROM tb
WHERE Date1 <= @dt2
OR Date2 >= @dt1
GO
DECLARE @tb TABLE(ID INT , Date1 DATETIME , Date2 DATETIME)
INSERT @tb SELECT 1,'2005-1-1','2005-6-9'
UNION ALL SELECT 2,'2005-7-2','2005-7-9'
UNION ALL SELECT 2,'2005-3-2','2005-5-9'
DECLARE @sdate DATETIME,@edate DATETIME
SET @sdate='2005-4-1'
SET @edate='2005-6-7'
SELECT Date1,Date2,sd,ed,
ISNULL(DATEDIFF(dd,
CASE WHEN sd>Date1 THEN sd ELSE Date1 END,
CASE WHEN ed>Date2 THEN Date2 ELSE ed END),0)
FROM @tb a
LEFT JOIN (SELECT @sdate sd,@edate ed) b
ON a.date2>=b.sd AND a.date1<=b.ed
select datediff(dd,
case when @sDate<=Date1 then Date1
when @sDate>=Date2 then Date2
else @sDate
end,
case when @eDate<=Date1 then Date1
when @eDate>=Date2 then Date2
else @eDate
end)
from 表