CREATE TABLE test
(
    startTime SMALLDATETIME,
    endTime SMALLDATETIME    
)
INSERT INTO test(startTime,    endTime)
SELECT '2012-01-01','2012-03-01' UNION ALL 
SELECT '2012-04-10','2012-07-10' UNION ALL
SELECT '2012-08-01','2012-10-01'

--如给一时间：‘2012-06-01’,则在第一条数据中2个月+第二条数据2个月共4个月

CREATE TABLE test
(
    startTime SMALLDATETIME,
    endTime SMALLDATETIME    
)
INSERT INTO test(startTime,    endTime)
SELECT '2012-01-01','2012-03-01' UNION ALL 
SELECT '2012-04-10','2012-07-10' UNION ALL
SELECT '2012-08-01','2012-10-01'

--如给一时间：‘2012-06-01’,则在第一条数据中2个月+第二条数据2个月共4个月

select sum( case when datediff(mm,startTime,'2012-06-01') > datediff(mm,startTime,endTime) 
then datediff(mm,startTime,endTime) else datediff(mm,startTime,'2012-06-01')end ) Num 
from test where startTime <='2012-06-01'
/*Num
-----------
4

(1 行受影响)
*/

------解决方案--------------------
SQL code
SELECT COUNT(starttime)+COUNT(endtime) FROM test WHERE  startTime<='2012-06-01'
 /*
 -----------
 4
 
 (1 行受影响)
 
 */

------解决方案--------------------

SQL code
declare @dt datetime
set @dt='2012-06-01'
select sum(datediff(mm,starttime,case when endtime>@dt then @dt else endtime end))
from test
where starttime<=@dt

------解决方案--------------------
SQL code
USE Test
GO


--IF OBJECT_ID('test')IS NOT NULL
--    DROP TABLE test
CREATE TABLE test
(
    startTime SMALLDATETIME,
    endTime SMALLDATETIME    
)
INSERT INTO test(startTime,    endTime)
SELECT '2012-01-01','2012-03-01' UNION ALL 
SELECT '2012-04-10','2012-07-10' UNION ALL
SELECT '2012-08-01','2012-10-01'



DECLARE @Date DATETIME
SET @Date='2012-06-01'


SELECT SUM(DATEDIFF(mm,starttime,CASE WHEN endTime>@Date THEN @Date ELSE endTime END)) FROM Test WHERE startTime<@Date