首先感謝: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
-- --------------------------------------------------------------------------------
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
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