計算年資(几年几月几日)的SQL語句

首先感謝:bzscs(沙虫 我爱小美) 和lxzm1001(*悠悠蓝星*)

偶的問題貼:http://community.csdn.net/Expert/topic/5009/5009969.xml?temp=.5809442

1.bzscs(沙虫 我爱小美)用函數的好辦法:

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 )
if   ( month ( @now ) = month ( @time ))  and  ( day ( @now ) < day ( @time ))  or  ( month ( @now ) < month ( @time ))
   set   @year   =   @year - 1

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

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

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

GO

-- -----------------------------------------------------------------------------------
-- 調用函數 :
-- 1.某一具體日期
declare   @kk   nvarchar ( 10 )
select   @kk   =   [ dbo ] . [ calc_date ] ( ' 2001-11-28 ' , getdate ())
print   @kk
-- 2.數據表的'入職日期'字段
select  empno,indate, [ dbo ] . [ calc_date ] (indate, getdate ())  from  employee 

-- --------------------------------------------------------------------------------

2.自己的笨辦法:新增了個表,還用了游標

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[tmpemp] ' )  and   OBJECTPROPERTY (id, N ' IsUserTable ' )  =   1 )
drop   table   [ dbo ] . [ tmpemp ]
GO

CREATE   TABLE   [ dbo ] . [ tmpemp ]  (
  [ empno ]   [ char ]  ( 8 ) COLLATE Chinese_Taiwan_Stroke_CI_AS  NOT   NULL  ,
  [ indate ]   [ datetime ]   NULL  ,
  [ timelong ]   [ varchar ]  ( 20 ) COLLATE Chinese_Taiwan_Stroke_CI_AS  NULL
)  ON   [ PRIMARY ]
GO


ALTER   TABLE   [ dbo ] . [ tmpemp ]   WITH   NOCHECK   ADD  
  CONSTRAINT   [ PK_tmpemp ]   PRIMARY   KEY    CLUSTERED  
 (
   [ empno ]
 )   ON   [ PRIMARY ]  
GO

insert   into   tmpemp  SELECT   empno,indate, ''    from  employee  where  flag = 1


declare   @year   int , @month   int , @day   int , @emp_no   char ( 8 ), @time   datetime
declare  cur02  cursor   for  
  select  empno,indate
    from  tmpemp

open  cur02
fetch   next    from  cur02  into   @emp_no , @time

while   @@fetch_status <>- 1
     begin


  select   @year   =   datediff (yy, @time , getdate ())
  if   ( month ( getdate ()) = month ( @time ))  and  ( day ( getdate ()) < day ( @time ))  or   ( month ( getdate ()) < month ( @time ))
  set   @year = @year - 1

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

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

  print    @emp_no + '   '   + convert ( varchar , @time , 111 )  + '    '   + cast ( @year   as   varchar )  +   ' 年 ' +   cast ( @month   as   varchar ) + ' 個月 ' + cast ( @day   as   varchar ) + ' 天 '


update  tmpemp  set  timelong = cast ( @year   as   varchar )  +   ' 年 ' +   cast ( @month   as   varchar ) + ' 個月 ' + cast ( @day   as   varchar ) + ' 天 '   where  empno = @emp_no

fetch   next    from  cur02  into   @emp_no , @time   
end

close  cur02
deallocate  cur02

-- 調用--
select  empno,indate,timelong  as  年資  from  tmpemp