select datediff(month,'2009-11-02','2009-12-01') 不知道你要怎么个月差异上面的命令结果也是1但是其实按照常识差异不是一个月,是差一天一个月这有段现成的代码可以帮助你算出常识上的差异 declare @t table(a datetime,b datetime); insert @t select '2009-11-02','2009-12-01' UNION ALL select '2009-11-01','2009-12-01' -->?? select a,b, ltrim(case when (month(b)=month(a) and day(b)>=day(a)) or month(b)>month(a) then datediff(year,a,b) else datediff(year,a,b)-1 end)+'年' +ltrim(case when day(b)>=day(a) --and month(b)>=month(a) then datediff(month,a,b)%12 else (datediff(month,a,b)-1)%12 end)+'月' +ltrim(case when day(b)>=day(a) then day(b)-day(a) else datediff(dd,convert(varchar(8),dateadd(mm,-1,b),120)+'01',b)+1-day(a) end)+'日' as '日期差' from (select (case when a>b then b else a end) as a, (case when a>b then a else b end) as b from @t ) t;a b 日期差 2009-11-02 00:00:00.000 2009-12-01 00:00:00.000 0年0月29日 2009-11-01 00:00:00.000 2009-12-01 00:00:00.000 0年1月0日
不知道你要怎么个月差异上面的命令结果也是1但是其实按照常识差异不是一个月,是差一天一个月这有段现成的代码可以帮助你算出常识上的差异
declare @t table(a datetime,b datetime);
insert @t
select '2009-11-02','2009-12-01' UNION ALL
select '2009-11-01','2009-12-01'
-->??
select a,b,
ltrim(case
when (month(b)=month(a) and day(b)>=day(a)) or month(b)>month(a)
then datediff(year,a,b)
else
datediff(year,a,b)-1
end)+'年'
+ltrim(case
when day(b)>=day(a) --and month(b)>=month(a)
then datediff(month,a,b)%12
else
(datediff(month,a,b)-1)%12
end)+'月'
+ltrim(case
when day(b)>=day(a)
then day(b)-day(a)
else
datediff(dd,convert(varchar(8),dateadd(mm,-1,b),120)+'01',b)+1-day(a)
end)+'日' as '日期差'
from
(select (case when a>b then b else a end) as a,
(case when a>b then a else b end) as b from @t ) t;a b 日期差
2009-11-02 00:00:00.000 2009-12-01 00:00:00.000 0年0月29日
2009-11-01 00:00:00.000 2009-12-01 00:00:00.000 0年1月0日