一个关于时间处理的句子
怎样用把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)


------解决方案--------------------
insert into tblOnLeave(attID,LeaveDate)
select distinct attID,LeaveDate
from
(select attID,LeaveDate=convert(varchar(10),LeaveDate,120)
from tblEmployeeOnLeave
)A
------解决方案--------------------
把请假记录(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)

上面的不对啊~
------解决方案--------------------
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
------解决方案--------------------
哦写错啦

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


------解决方案--------------------
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
BEGIN

set @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
END

CLOSE 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