skyline0217

1.年資(函數版)

create   function   [dbo].[calc_date](@time   smalldatetime,@now   smalldatetime)
returns   nvarchar(10)
as
begin
declare   @year   int,@month   int,@day   int

select   @year   =   datediff(yy,@time,@now)

select   @month   =   datediff(month,@time,@now)-12*@year
if(day(@now) 0 and @month<0)
	set @year = @year-1
if(@month<0)
	set @month = @month+12


return   cast(@year   as   varchar)   +   '年 '+   cast(@month   as   varchar)+ '月 '+cast(@day   as   varchar)+ '天 '
end
go



declare   @kk   nvarchar(10)
select   @kk   =   [dbo].[calc_date]( '2010-07-26','2011-01-25')
--select   [dbo].[calc_date](indate,getdate())   from   employee  
print   @kk


--select dbo.calc_date( '2010-12-1','2011-01-25')

2.

declare   @year   int,@month   int,@day   int
declare   @time   varchar(10)

set   @time   =   '2010-07-26'
select   @year   =   datediff(yy,@time,getdate())

select   @month   =   datediff(month,@time,getdate())-12*@year
if(day(getdate()) < 0)

select   @day   =   datediff(dd,dateadd(month,(12*@year+@month),@time),getdate())

if(@year >0 and @month<0)
	set @year = @year-1
if(@month<0)
	set @month = @month+12
	
print   cast(@year   as   varchar)   +   '年 '+   cast(@month   as   varchar)+ '個月 '+cast(@day   as   varchar)+ '天 '


--===>  0年 5個月 30天