生成指定范围内的随机时间段(转)

示例一:生成指定范围内的随机时间.
create table Ctime(id int ,EmyeeID int,IOTime datetime,IODate datetime)
insert into Ctime values(1,'1001','2007-8-1 7:30:21' ,'2007-8-1')
insert into Ctime values(2,'1001','2007-8-1 17:30:42','2007-8-1')
insert into Ctime values(3,'1002','2007-8-1 7:30:21' ,'2007-8-1')
insert into Ctime values(4,'1002','2007-8-1 17:30:42','2007-8-1')

--插入所有下班數據,時間範圍11:30:00--12:00:00
--語句中41400是11:30:00的秒數,1800是30分鐘的秒數,可自行調整
INSERT INTO Ctime(EmyeeID,IOTime,IODate)
SELECT EmyeeID,
DATEADD(second,41400+CAST(rand(checksum(newid()))*10000 AS INT) %1800,IODATE)time0,
DATEADD(second,41400+ABS(checksum(newid()))%1800,IODATE)time1,
IODate FROM Ctime GROUP BY IODate,EmyeeID

--插入所有上班數據,時間範圍13:00:00--13:30:00
--語句中46800是11:30:00的秒數,2100是30分鐘的秒數,可自行調整
INSERT INTO Ctime(EmyeeID,IOTime,IODate)
SELECT EmyeeID,
DATEADD(second,46800+CAST(rand(checksum(newid()))*10000 AS INT) %2100,IODATE),
IODate FROM Ctime GROUP BY IODate,EmyeeID

结果:
EmyeeID     time0                   time1                   IODate
----------- ----------------------- ----------------------- -----------------------
1001        2007-08-01 11:44:09.000 2007-08-01 11:35:47.000 2007-08-01 00:00:00.000
1002        2007-08-01 11:36:07.000 2007-08-01 11:50:36.000 2007-08-01 00:00:00.000

(2 行受影响)

关键技巧:
--插入所有下班數據,時間範圍11:30:00--12:00:00
--語句中41400是11:30:00的秒數,1800是30分鐘的秒數,可自行調整
DATEADD(second,41400+ABS(checksum(newid()))%1800,IODATE)time1,

引用这里:http://topic.csdn.net/u/20071112/09/f1e09827-2f17-44fe-b70b-533b5afc1c66.html

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/zhou__zhou/archive/2007/11/13/1881455.aspx