27,579
社区成员
发帖
与我相关
我的任务
分享
----我又来蹭分!
-----------------------------------------------------------
--不虑日期B比日期A小的情况(日期B一定大于日期A)
declare @t table(a datetime,b datetime);
insert @t
select
'2008-07-01','2009-04-01' union all select
'2006-04-08','2009-07-11' union all select
'2008-07-07','2009-07-06'
-->查询
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 @t t;
-------------------------------------------------------------------------------
--考虑日期B比日期A小的情况
declare @t table(a datetime,b datetime);
insert @t
select
'2008-07-01','2009-04-01' union all select
'2009-07-11','2006-04-08' union all select
'2008-07-07','2009-07-06'
-->查询
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;
declare @t table(a datetime,b datetime);
insert @t
select
'2008-07-01','2009-04-01' union all select
'2009-07-11','2006-04-08' union all select
'2008-07-07','2009-07-06'
-->查询
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)+'日'
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;
----我又来蹭分!
--考虑日期B比日期A小的情况
declare @t table(a datetime,b datetime);
insert @t
select
'2008-07-01','2009-04-01' union all select
'2009-07-11','2006-04-08'
-->查询
select
ltrim(case
when month(a)<=month(b)
then datediff(yy,a,b)
else
datediff(yy,a,b)-1
end)+'年'
+ltrim(case
when day(a)<=day(b)
then datediff(mm,a,b)%12
else
datediff(mm,a,b)%12-1
end)+'月'
+ltrim(case
when day(a)<=day(b)
then day(b)-day(a)
else
datediff(dd,convert(varchar(8),dateadd(mm,-1,b),120)+'01',b)+1-day(a)
end)+'日'
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;
declare @t table(a datetime,b datetime);
insert @t select '2008-07-01','2009-04-01';
-->查询
select
ltrim(case
when month(a)<=month(b)
then datediff(yy,a,b)
else
datediff(yy,a,b)-1
end)+'年'
+ltrim(case
when day(a)<=day(b)
then datediff(mm,a,b)%12
else
datediff(mm,a,b)%12-1
end)+'月'
+ltrim(case
when day(a)<=day(b)
then day(b)-day(a)
else
datediff(dd,convert(varchar(8),dateadd(mm,-1,b),120)+'01',b)+1-day(a)
end)+'日'
from
@t
/**
------------------------------------------
0年9月0日
(所影响的行数为 1 行)
**/
天:
datediff(dd,a,b)
月
datediff(mm,a,b)
年
datediff(yy,a,b)