求一存储过程。根据出生年月日得出年龄，要求精确到岁，月，日

[hanker1314520]

1981-01-01 得到 26岁
2006-12-01 得到 1月
2007-01-01 得到 14天
要求返回年龄和年龄单位 ,26岁,1月,14天
寻求最简单的写法

[hanker1314520]

UP!!!!!!

[marco08]

declare @dt datetime
set @dt='1981-01-01'
select
(
datediff(year, @dt, getdate())-
case when dateadd(year, datediff(year, @dt, getdate()), @dt)>getdate()
then 1 else 0 end
) as 年龄,
datediff(month, dateadd( year, datediff(year, @dt, getdate()), @dt ), getdate()) as 月,
datediff(day, dateadd(month, datediff(month, @dt, getdate()), @dt), getdate()) as 天

--result
年龄 月 天
----------- ----------- -----------
26 0 14

(1 row(s) affected)

[marco08]

declare @dt datetime
set @dt='1981-01-01'
select
(
datediff(year, @dt, getdate())-
case when dateadd(year, datediff(year, @dt, getdate()), @dt)>getdate()
then 1 else 0 end
) as 年龄,
abs( datediff(month, dateadd( year, datediff(year, @dt, getdate()), @dt ), getdate()) ) as 月,
abs( datediff(day, dateadd(month, datediff(month, @dt, getdate()), @dt), getdate()) ) as 天

[achongsky]

declare @dt datetime
set @dt='1981-08-20'
select
(
datediff(year, @dt, getdate())-
case when dateadd(year, datediff(year, @dt, getdate()), @dt)>getdate()
then 1 else 0 end
) as 年龄,
datediff(month, dateadd( year, datediff(year, @dt, getdate()), @dt ), getdate()) as 月,
datediff(day, dateadd(month, datediff(month, @dt, getdate()), @dt), getdate()) as 天

--------

25 -7 -5

[leo_lesley]

select cast(DATEDIFF( year ,@start_day,getdate()) as varchar(10)) + '年'
select cast(DATEDIFF( month , '2006-12-01',getdate()) as varchar(10)) + '月'
select cast(DATEDIFF( day , '2007-01-01', getdate()) as varchar(10)) + '日'

[achongsky]

还是不对~
----
declare @dt datetime
set @dt='1981-08-20'
select
(
datediff(year, @dt, getdate())-
case when dateadd(year, datediff(year, @dt, getdate()), @dt)>getdate()
then 1 else 0 end
) as 年龄,
abs( datediff(month, dateadd( year, datediff(year, @dt, getdate()), @dt ), getdate()) ) as 月,
abs( datediff(day, dateadd(month, datediff(month, @dt, getdate()), @dt), getdate()) ) as 天

----
25 7 5

[wcbgyjs]

declare @dt datetime, @year int, @month int
set @dt='1981-01-01'

select @year = datediff(year, @dt, getdate()), @month = datediff(month, @dt, getdate())

if(@year > 0 and dateadd(year, @year, @dt)<=getdate())
begin
select cast(@year as varchar(10))+'岁'
end else
begin
if(@month > 1)
begin
if(dateadd(month, @month, @dt)>getdate())
begin
set @month = @month - 1
end

select cast(@month as varchar(10))+'月'
end else
if(@month > 0 and dateadd(month, @month, @dt)<=getdate())
begin
select cast(@month as varchar(10))+'月'
end else
begin
select cast(datediff(day, @dt, getdate()) as varchar(10))+'天'
end
end

[wcbgyjs]

上一个没考虑年的特殊情况。
--------------------------------
declare @dt datetime, @year int, @month int
set @dt='1981-12-01'

select @year = datediff(year, @dt, getdate()), @month = datediff(month, @dt, getdate())

if(@year > 1)
begin
if(dateadd(year, @year, @dt)>getdate())
begin
set @year = @year - 1
end

select cast(@year as varchar(10))+'岁'
end else
if(@year > 0 and dateadd(year, @year, @dt)<=getdate())
begin
select cast(@year as varchar(10))+'岁'
end else
begin
if(@month > 1)
begin
if(dateadd(month, @month, @dt)>getdate())
begin
set @month = @month - 1
end

select cast(@month as varchar(10))+'月'
end else
if(@month > 0 and dateadd(month, @month, @dt)<=getdate())
begin
select cast(@month as varchar(10))+'月'
end else
begin
select cast(datediff(day, @dt, getdate()) as varchar(10))+'天'
end
end