求两段时间之间的月数？

Question

select datediff(month,startdate,enddate) + 1

Answer 1

select datediff(mm,date1,date2)+(case when (day(date2)-day(date1))<15 then 0 else 1 end)

Answer 2

select case when day(enddate) - day(startdate) > 14
               then datediff(month,startdate,enddate) + 1
            else  datediff(month,startdate,enddate)
       end

Answer 3

create table #
(
  dt1 varchar(10),
  dt2 varchar(10)
)
insert #
select '2005-01-01','2005-01-31' union 
select '2005-01-01','2005-02-14' union
select '2005-01-01','2005-02-15' select dt1,dt2,[月份]=(case when (day(dt2))>=15 then 1+datediff(month,dt1,dt2) else datediff(month,dt1,dt2) end) from #drop table #

Answer 4

sorry我没说清楚，这两段时间不一定是连续月之间，比如说2002-12-31到2005-01-01只算24个月

Answer 5

是这个嘛？
create table #
(
  dt1 varchar(10),
  dt2 varchar(10)
)
insert #
select '2005-01-01','2005-01-31' union 
select '2005-01-01','2005-02-14' union
select '2005-01-01','2005-02-15' union
select '2002-12-31','2005-01-01' select dt1,dt2,[月份]=(
                       case 
                       when (day(dt2)-day(dt1))>=14 then datediff(month,dt1,dt2)+1
                       when (day(dt2)-day(dt1))<0 then datediff(month,dt1,dt2)-1 
                       else datediff(month,dt1,dt2) 
                       end
                      ) 
from #drop table #--结果
/*dt1        dt2        月份          
---------- ---------- ----------- 
2002-12-31 2005-01-01 24
2005-01-01 2005-01-31 1
2005-01-01 2005-02-14 1
2005-01-01 2005-02-15 2（所影响的行数为 4 行）
*/

Answer 6

select date1,date2,datediff(dd,date1,date2),floor(datediff(dd,date1,date2)/30)
from table1