DECLARE @BTime DATETIME,@ETime DATETIME;
DECLARE @Temp TABLE (
ID BIGINT IDENTITY(1,1),
aid BIGINT,
newcode BIGINT,
btime DATETIME,
etime DATETIME
);
DECLARE @Temp2 TABLE (
aid BIGINT,
newcode BIGINT,
dayTime VARCHAR(100)
);
INSERT INTO @Temp SELECT [id]
,[newCode]
,[checkTime]
,[auctionEndTime]
FROM [houseLibary]
--SELECT * FROM @Temp
DECLARE @Cnt BIGINT = (SELECT COUNT(aid) FROM @Temp);
DECLARE @I BIGINT = 1;
WHILE @I <= @Cnt
BEGIN
DECLARE @DayBetween BIGINT = (SELECT DATEDIFF(DAY,btime,etime) FROM @Temp WHERE id = @I)
DECLARE @BTimeTemp DATETIME = (SELECT btime FROM @Temp WHERE id = @I)
DECLARE @ETimeTemp DATETIME = (SELECT etime FROM @Temp WHERE id = @I)
DECLARE @tempNewcode BIGINT = (SELECT newcode FROM @Temp WHERE id = @I)
DECLARE @AID BIGINT = (SELECT aid FROM @Temp WHERE id = @I)
DECLARE @J BIGINT = 0;
--遍历开始
WHILE @J <= @DayBetween
BEGIN
INSERT INTO @Temp2
( aid, newcode, dayTime )
VALUES ( @AID, -- aid - bigint
@tempNewcode, -- newcode - bigint
SUBSTRING(CONVERT(VARCHAR(100),DATEADD(DAY,@J,@BTimeTemp),120),0,12) -- dayTime - varchar(100)
)
SET @J = @J + 1;
END
--遍历结束
SET @I = @I + 1;
END
SELECT * FROM @Temp2