给定两个日期，求精准时间间隔——，给出最优答案，立即结贴

给定两个日期，求精准时间间隔——在线等，给出最优答案，立即结贴
问题描述如下：
有三个日期
A = '2012-08-15 00:00:00.000'
B = '2013-08-01 00:00:00.000' 
C = '2013-08-31 00:00:00.000' 

求B和A、C和A的精准时间间隔

用下面方法
SELECT DATEDIFF ( MM , '2012-08-15 00:00:00.000' ,'2013-08-01 00:00:00.000' )
返回 12

SELECT DATEDIFF ( MM , '2012-08-15 00:00:00.000' ,'2013-08-31 00:00:00.000' )
返回 12

不符合实际需要，期望结果如下
B和A 精准时间间隔 返回 11.5
C和A 精准时间间隔 返回 12.5


求高人给出求精准时间间隔的函数

[解决办法]

SQL code

declare @st datetime,        @et datetime,        @MaxT    datetimeset @st = '2012-08-15 00:00:00.000'set @et = '2013-08-31 00:00:00.000' set @MaxT = @stwhile(@maxt < @et)begin    set @MaxT = dateadd(mm,1,@maxt)endif (@MaxT > @et)begin    set @MaxT = dateadd(mm,-1,@maxt)endselect DATEDIFF (mm, @st ,@MaxT )+ datediff(day,@MaxT,@et)*1.0/day(dateadd(mm,1,@MaxT)-day(@MaxT))/*---------------------------------------12.516129032258(1 行受影响)*/
[解决办法]
SQL code
declare @a datetime,        @b datetime,        @c datetimeset @a = '2012-08-15 00:00:00.000'set @b = '2013-08-01 00:00:00.000'set @c = '2013-08-31 00:00:00.000'  --假设@b是恒大于@a的，大的多少未知SELECT CASE WHEN DATEADD(mm,DATEDIFF(mm,@a,@b),@a)>=@bTHEN LTRIM(DATEDIFF(mm,@a,@b)-1)+'.'+LTRIM(DATEDIFF(dd,DATEADD(mm,DATEDIFF(mm,@a,@b)-1,@a),@b))ELSE LTRIM(DATEDIFF(mm,@a,@b))+'.'+LTRIM(DATEDIFF(dd,DATEADD(mm,DATEDIFF(mm,@a,@b),@a),@b))END SELECT CASE WHEN DATEADD(mm,DATEDIFF(mm,@a,@c),@a)>=@cTHEN LTRIM(DATEDIFF(mm,@a,@c)-1)+'.'+LTRIM(DATEDIFF(dd,DATEADD(mm,DATEDIFF(mm,@a,@c)-1,@a),@c))ELSE LTRIM(DATEDIFF(mm,@a,@c))+'.'+LTRIM(DATEDIFF(dd,DATEADD(mm,DATEDIFF(mm,@a,@c),@a),@c))END /*-------------------------11.17(1 行受影响)-------------------------12.16(1 行受影响)*/
[解决办法]
SQL code
declare @A datetime,@B datetime,@C datetimeset @A='2012-08-15 00:00:00.000'set @B='2013-08-01 00:00:00.000' set @C='2013-08-31 00:00:00.000' select convert(decimal(18,1),datediff(mm,convert(varchar(7),@A,120)+'-01',convert(varchar(7),@B,120)+'-01'))+convert(decimal(18,1),(day(@B)-day(@A))/30.0) B减A,convert(decimal(18,1),datediff(mm,convert(varchar(7),@A,120)+'-01',convert(varchar(7),@C,120)+'-01'))+convert(decimal(18,1),(day(@C)-day(@A))/30.0) C减A/*B减A                                     C减A--------------------------------------- ---------------------------------------11.5                                    12.5(1 row(s) affected)*/