22,209
社区成员
发帖
与我相关
我的任务
分享
--1。不考虑日期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;
-------------------------------------------------------------------------------
--2。考虑日期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 @yy int,@mm int,@dd int,@date_a varchar(15),@date_b varchar(15)
select @date_a = '2009-03-01',@date_b='2008-03-01'
if cast(@date_a as datetime)<=cast(@date_b as datetime)
begin
set @yy=datediff(yy,@date_a,@date_b)-1
if @yy<=0
begin
set @yy=0
end
select @mm=datediff(mm,dateadd(yy,@yy,@date_a),@date_b)-1
if @mm<=0
begin
set @mm=0
end
select @dd=datediff(dd,dateadd(mm,@mm,@date_a),@date_b)
if @dd<=0
begin
set @dd=0
end
if @dd>=(datediff(dd,dateadd(mm,-1,convert(char(8),@date_b,120)+'1'),dateadd(dd,-1,convert(char(8),@date_b,120)+'1'))+1)
begin
select @dd=@dd-(datediff(dd,dateadd(mm,-1,convert(char(8),@date_b,120)+'1'),dateadd(dd,-1,convert(char(8),@date_b,120)+'1'))+1),@mm=@mm+1
end
if @mm>=12
begin
select @yy=@yy+1,@mm=@mm-12
end
end
else
begin
set @yy=datediff(yy,@date_b,@date_a)-1
if @yy<=0
begin
set @yy=0
end
select @mm=datediff(mm,dateadd(yy,@yy,@date_b),@date_a)-1
if @mm<=0
begin
set @mm=0
end
select @dd=datediff(dd,dateadd(mm,@mm,@date_b),@date_a)
if @dd<=0
begin
set @dd=0
end
if @dd>=(datediff(dd,dateadd(mm,-1,convert(char(8),@date_a,120)+'1'),dateadd(dd,-1,convert(char(8),@date_a,120)+'1'))+1)
begin
select @dd=@dd-(datediff(dd,dateadd(mm,-1,convert(char(8),@date_a,120)+'1'),dateadd(dd,-1,convert(char(8),@date_a,120)+'1'))+1),@mm=@mm+1
end
if @mm>=12
begin
select @yy=@yy+1,@mm=@mm-12
end
end
select @yy as 年,@mm as 月,@dd as 日
select cast((datediff(month,@s,@e)-(case when day(@s)>day(@e)then 1 else 0 end))/12 as varchar)+'年'
+cast((datediff(month,@s,@e)-(case when day(@s)>day(@e)then 1 else 0 end))%12 as varchar)+'月'
+cast(datediff(day,dateadd(month,datediff(month,@s,@e)-(case when day(@s)>day(@e)then 1 else 0 end),@s),@e)as varchar)+'日'