declare @s varchar(10) set @s='20120509'select LTrim(datediff(year,Cast (@s as datetime), getdate()))+'年'+Ltrim(datediff(mm,Cast (@s as datetime), getdate())%12)+'月'+Ltrim(datediff(day,Cast (@s as datetime), getdate())%day(getdate()))+'天'--2年4月7天 应该是7天吧
declare @s varchar(8) set @s='20120509' select cast(datediff(mm,@s,getdate())/12 as varchar)+'年' +cast(case when datepart(dd,getdate())>=datepart(dd,@s) then datediff(mm,@s,getdate())%12 else datediff(mm,@s,getdate())%12 -1 end as varchar)+'月' +cast(case when datepart(dd,getdate())>=datepart(dd,@s) then datediff(dd,dateadd(mm,datediff(mm,@s,getdate()),@s),getdate()) else datediff(dd,dateadd(mm,datediff(mm,@s,getdate()) -1,@s) ,getdate()) end as varchar)+'日'
看这个行不行DECLARE @s DATETIME, @e DATETIMESET @s='2011-9-10' SET @e='2014-9-10'IF @e >= @s SELECT Cast(CASE WHEN Cast(Datepart(mm, @e) AS VARCHAR) + RIGHT('00' + Cast(Datepart(dd, @e) AS VARCHAR), 2) >= Cast(Datepart(mm, @s) AS VARCHAR) + RIGHT('00' + Cast(Datepart(dd, @s) AS VARCHAR), 2) THEN Datediff(yy, @s, @e) ELSE Datediff(yy, @s, @e) - 1 END AS VARCHAR) + '年' + Cast(CASE WHEN Datepart(dd, @e) >= Datepart(dd, @s) THEN Datediff(mm, @s, @e)%12 ELSE CASE WHEN Datepart(mm, @s) = Datepart(mm, @e) THEN 11 ELSE Datediff(mm, @s, @e)%12 - 1 END END AS VARCHAR) + '月' + Cast(CASE WHEN Datepart(dd, @e) >= Datepart(dd, @s) THEN Datediff(dd, Dateadd(mm, Datediff(mm, @s, @e), @s), @e) ELSE Datediff(dd, Dateadd(mm, Datediff(mm, @s, @e) - 1, @s), @e) END AS VARCHAR) + '日' AS '年龄' ELSE SELECT '开始日期不能大于结束日期' AS error
declare @birthday smalldatetime set @birthday='20131210' select cast(floor(datediff(dd,@birthday,getdate())/365) as varchar)+'歲' +case when day(@birthday)<=day(getdate()) then cast( datediff(month,@birthday,getdate())%12 as varchar) else cast( datediff(month,@birthday,getdate())%12-1 as varchar) end +'個月'+ case when day(@birthday)<=day(getdate()) then cast(datediff(day,dateadd(month,datediff(month,@birthday,getdate()),@birthday),getdate()) as varchar) else cast(datediff(day,dateadd(month,datediff(month,@birthday,getdate())-1,@birthday),getdate()) as varchar) end +'天'
set @s='20120509'select LTrim(datediff(year,Cast (@s as datetime), getdate()))+'年'+Ltrim(datediff(mm,Cast (@s as datetime), getdate())%12)+'月'+Ltrim(datediff(day,Cast (@s as datetime), getdate())%day(getdate()))+'天'--2年4月7天
应该是7天吧
set @s='20120509'
select cast(datediff(mm,@s,getdate())/12 as varchar)+'年'
+cast(case when datepart(dd,getdate())>=datepart(dd,@s) then datediff(mm,@s,getdate())%12 else datediff(mm,@s,getdate())%12 -1 end as varchar)+'月'
+cast(case when datepart(dd,getdate())>=datepart(dd,@s) then datediff(dd,dateadd(mm,datediff(mm,@s,getdate()),@s),getdate()) else datediff(dd,dateadd(mm,datediff(mm,@s,getdate()) -1,@s) ,getdate()) end as varchar)+'日'
@e DATETIMESET @s='2011-9-10'
SET @e='2014-9-10'IF @e >= @s
SELECT Cast(CASE
WHEN Cast(Datepart(mm, @e) AS VARCHAR) + RIGHT('00' + Cast(Datepart(dd, @e) AS VARCHAR), 2) >= Cast(Datepart(mm, @s) AS VARCHAR) + RIGHT('00' + Cast(Datepart(dd, @s) AS VARCHAR), 2) THEN Datediff(yy, @s, @e)
ELSE Datediff(yy, @s, @e) - 1
END AS VARCHAR) + '年' + Cast(CASE
WHEN Datepart(dd, @e) >= Datepart(dd, @s) THEN Datediff(mm, @s, @e)%12
ELSE
CASE
WHEN Datepart(mm, @s) = Datepart(mm, @e) THEN 11
ELSE Datediff(mm, @s, @e)%12 - 1
END
END AS VARCHAR) + '月' + Cast(CASE
WHEN Datepart(dd, @e) >= Datepart(dd, @s) THEN Datediff(dd, Dateadd(mm, Datediff(mm, @s, @e), @s), @e)
ELSE Datediff(dd, Dateadd(mm, Datediff(mm, @s, @e) - 1, @s), @e)
END AS VARCHAR) + '日' AS '年龄'
ELSE
SELECT '开始日期不能大于结束日期' AS error
set @birthday='20131210'
select cast(floor(datediff(dd,@birthday,getdate())/365) as varchar)+'歲'
+case when day(@birthday)<=day(getdate()) then cast( datediff(month,@birthday,getdate())%12 as varchar)
else cast( datediff(month,@birthday,getdate())%12-1 as varchar) end
+'個月'+ case when day(@birthday)<=day(getdate()) then
cast(datediff(day,dateadd(month,datediff(month,@birthday,getdate()),@birthday),getdate()) as varchar)
else cast(datediff(day,dateadd(month,datediff(month,@birthday,getdate())-1,@birthday),getdate()) as varchar)
end
+'天'