SELECT DATEDIFF(day, '2007-03-10', getdate())/30 as Months ,DATEDIFF(day, '2007-03-10', getdate())%30 as Days
declare @t datetime select @t='2007-3-20' select case when day(@t)<=day(getdate()) then convert(varchar(10),datediff(month,@t,getdate()))+'个月零'+convert(varchar(10),datediff(day,@t,dateadd(month,-datediff(month,@t,getdate()),getdate())))+'天' else convert(varchar(10),datediff(month,@t,getdate())-1)+'个月零'+convert(varchar(10),datediff(day,dateadd(month,datediff(month,@t,getdate())-1,@t),getdate()))+'天' end
一楼的并不准确,因为有的月份是31天,有的是29、28天。并不都是30。所以不能单纯的取余的。 应该这样:declare @date datetime set @date='2007-3-10' select cast(datediff(dd,@date,getdate()) as varchar)+'天' [总天数], cast(datediff(mm,@date,getdate()) as varchar)+'个月'+ case datediff(dd,dateadd(mm,datediff(mm,@date,getdate()),@date),getdate()) when 0 then '整' else '零'+cast(datediff(dd,dateadd(mm,datediff(mm,@date,getdate()),@date),getdate()) as varchar) +'天' end [月份] 返回:219天 7个月零5天
DECLARE @D DATETIME SET @D='2007.03.10' SELECT CONVERT(VARCHAR,DATEDIFF(MM,@D,GETDATE()))+'个月'+CONVERT(VARCHAR,DATEDIFF(DD,DATEADD(MM,DATEDIFF(MM,@D,GETDATE()),@D),GETDATE()))+'天' --结果 ------------------------------------------------------------------ 7个月5天
select @t='2007-3-20'
select case when day(@t)<=day(getdate()) then convert(varchar(10),datediff(month,@t,getdate()))+'个月零'+convert(varchar(10),datediff(day,@t,dateadd(month,-datediff(month,@t,getdate()),getdate())))+'天'
else convert(varchar(10),datediff(month,@t,getdate())-1)+'个月零'+convert(varchar(10),datediff(day,dateadd(month,datediff(month,@t,getdate())-1,@t),getdate()))+'天'
end
一楼的并不准确,因为有的月份是31天,有的是29、28天。并不都是30。所以不能单纯的取余的。
应该这样:declare @date datetime
set @date='2007-3-10'
select cast(datediff(dd,@date,getdate()) as varchar)+'天' [总天数], cast(datediff(mm,@date,getdate()) as varchar)+'个月'+
case datediff(dd,dateadd(mm,datediff(mm,@date,getdate()),@date),getdate()) when 0 then '整'
else '零'+cast(datediff(dd,dateadd(mm,datediff(mm,@date,getdate()),@date),getdate()) as varchar) +'天' end [月份]
返回:219天 7个月零5天
DECLARE @D DATETIME
SET @D='2007.03.10'
SELECT CONVERT(VARCHAR,DATEDIFF(MM,@D,GETDATE()))+'个月'+CONVERT(VARCHAR,DATEDIFF(DD,DATEADD(MM,DATEDIFF(MM,@D,GETDATE()),@D),GETDATE()))+'天'
--结果
------------------------------------------------------------------
7个月5天