呵呵,套出來了,新增了個表,還用了游標,有沒有更好的寫法?----------------------if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmpemp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tmpemp] GOCREATE 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] GOinsert 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 tmpempopen cur02 fetch next from cur02 into @emp_no,@timewhile @@fetch_status<>-1 begin select @year = datediff(yy,@time,getdate()) if (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_nofetch next from cur02 into @emp_no,@time endclose cur02 deallocate cur02select empno,indate,timelong as 年資 from tmpemp
可以做个函数呀create function [dbo].[calc_date](@time smalldatetime,@now smalldatetime) returns nvarchar(10) as begin declare @year int,@month int,@day intselect @year = datediff(yy,@time,@now)select @month = datediff(month,@time,@now)-12*@year if(day(@now)<day(@time)) set @month = @month-1select @day = datediff(dd,dateadd(month,(12*@year+@month),@time),@now)return cast(@year as varchar) + '年'+ cast(@month as varchar)+'个月'+cast(@day as varchar)+'天' end godeclare @kk nvarchar(10) select @kk = [dbo].[calc_date]('2002-2-7',getdate()) --select [dbo].[calc_date](indate,getdate()) from employee print @kk
ltrim(month(getdate()-'2001-01-01'))+'月'+
ltrim(day(getdate()-'2001-01-01'))+'日'/*
----------------------------------
05年9月9日(所影响的行数为 1 行)
*/
ltrim(month(getdate()-'2001-02-28'))+'月'+
ltrim(day(getdate()-'2001-02-28'))+'日'/*
----------------------------------
5年7月13日(所影响的行数为 1 行)*/
你們給的語句得出的結果一樣,但計算出的結果不正確哦...2002-09-02 04.01.11 應該是:04.00.09
2006-05-24 00.04.21 應該是:00.03.18
2003-04-07 03.06.08 應該是:03.05.04
看来是台湾的兄弟姐妹,仔细讲一讲!
-----------非也..
米辦法,公司用繁體系統-_-#
declare @time varchar(10)set @time = '2001-2-28'
select @year = datediff(yy,@time,getdate())select @month = datediff(month,@time,getdate())-12*@year
if(day(getdate())<day(@time))
set @month = @month-1select @day = datediff(dd,dateadd(month,(12*@year+@month),@time),getdate())print cast(@year as varchar) + '年'+ cast(@month as varchar)+'个月'+cast(@day as varchar)+'天'
indate不是一个日期格式么?
-----------------------------
偶這里不是,是一個字段名稱,代表某職員的入職日期
drop table [dbo].[tmpemp]
GOCREATE 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]
GOinsert 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 tmpempopen cur02
fetch next from cur02 into @emp_no,@timewhile @@fetch_status<>-1
begin
select @year = datediff(yy,@time,getdate())
if (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_nofetch next from cur02 into @emp_no,@time
endclose cur02
deallocate cur02select empno,indate,timelong as 年資 from tmpemp
returns nvarchar(10)
as
begin
declare @year int,@month int,@day intselect @year = datediff(yy,@time,@now)select @month = datediff(month,@time,@now)-12*@year
if(day(@now)<day(@time))
set @month = @month-1select @day = datediff(dd,dateadd(month,(12*@year+@month),@time),@now)return cast(@year as varchar) + '年'+ cast(@month as varchar)+'个月'+cast(@day as varchar)+'天'
end
godeclare @kk nvarchar(10)
select @kk = [dbo].[calc_date]('2002-2-7',getdate())
--select [dbo].[calc_date](indate,getdate()) from employee
print @kk