一个关于时间处理的句子

Question

怎样用把tblEmployeeOnLeave(attID,StartTime,EndTime,ApprovedDays)表(请假表)中的ApprovedDays大于1(请假天数大于1)的记录拆分成一天一条的记录再插入到tblOnLeave(attID,LeaveDate)表?
如:把请假记录(10,2007/03/01 9:00:00:000,2007/03/03 18:00:00:000, 3 )拆分成
(10,2007/03/01 00:00:00:000)
(10,2007/03/02 00:00:00:000)
(10,2007/03/03 00:00:00:000)

Answer 1

insert into tblOnLeave(attID,LeaveDate)
select distinct attID,LeaveDate
from 
  (select attID,LeaveDate=convert(varchar(10),LeaveDate,120)
  from tblEmployeeOnLeave
  )A

Answer 2

declare @i int
set @i=1---处理第一天
insert into tblOnLeave(attID,LeaveDate)
select attid,StartTime from tblEmployeeOnLeave while @@rowcount>0
begin
insert into tblOnLeave(attID,LeaveDate)
select attid,dateadd(dd,1,StartTime) from tblEmployeeOnLeave 
where EndTime>=dateadd(dd,1,StartTime) set @i=@i+1
end

Answer 3

declare @i int
set @i=0
while @@rowcount>0
begin
 insert into tblOnLeave(attID,LeaveDate)
 select attID,convert(char(10),dateadd(day,@i,StartTime),120) 
 from tblEmployeeOnLeave
 where convert(char(10),EndTime,120)>convert(char(10),dateadd(day,@i,StartTime),120)
 and ApprovedDays>1
 set @i=@i+1
end

Answer 4

哦写错啦declare @i int
set @i=1---处理第一天
insert into tblOnLeave(attID,LeaveDate)
select attid,StartTime from tblEmployeeOnLeave while @@rowcount>0
begin
insert into tblOnLeave(attID,LeaveDate)
select attid,dateadd(dd,@i,StartTime) from tblEmployeeOnLeave 
where EndTime>dateadd(dd,@i,StartTime)set @i=@i+1
end

Answer 5

create table tblEmployeeOnLeave
(
attID int,
StartTime datetime,
EndTime datetime,
ApprovedDays int
)
create table tblOnLeave
(
attID int,
LeaveDate datetime
)
insert into tblEmployeeOnLeave select 10,'2007-03-01','2007-03-03',3
go
---------------------------
declare @i int
DECLARE @StartTime datetime
DECLARE @ApprovedDays int
DECLARE curtest CURSOR FOR 
select StartTime,ApprovedDays from tblEmployeeOnLeave
OPEN curtest
FETCH curtest INTO @StartTime,@ApprovedDays
WHILE @@FETCH_STATUS = 0
BEGINset @i=1
 while @ApprovedDays>0
begin
insert into tblOnLeave (LeaveDate) values(dateadd(day,@i,@StartTime))
select @ApprovedDays=@ApprovedDays-1,@i=@i+1
end
FETCH curtest INTO @StartTime,@ApprovedDays
ENDCLOSE curtest
DEALLOCATE curtest
select * from tblOnLeave
--结果
attID       LeaveDate
----------- -----------------------
NULL        2007-03-02 00:00:00.000
NULL        2007-03-03 00:00:00.000
NULL        2007-03-04 00:00:00.000

Answer 6

上面有错，设
WHILE @@FETCH_STATUS = 0
BEGINset @i=1--此处改为0