问题描述如下：
有三个日期
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' [email protected]@a的，大的多少未知SELECT CASE WHEN DATEADD(mm,DATEDIFF(mm,@a,@b),@a)>[email protected]THEN 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)>[email protected]THEN 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)*/