Example 1: generate a random time in a specified range.
Create Table ctime (ID int, emyeeid int, iotime datetime, iodate datetime)
Insert into ctime values (1, '20170101', '2017-8-1 7:30:21 ', '2017-8-1 ')
Insert into ctime values (2, '20170101', '2017-8-1 17:30:42 ', '2017-8-1 ')
Insert into ctime values (3, '20170101', '2017-8-1 7:30:21 ', '2017-8-1 ')
Insert into ctime values (4, '20170101', '2017-8-1 17:30:42 ', '2017-8-1 ')
-- Insert all off-duty data records, time period 11:30:00 -- 12:00:00
-- In a sentence, 41400 is the number of seconds at 11:30:00, and 1800 is the number of seconds at 30 minutes.
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
-- Insert all work data records, time periods 13:00:00 -- 13:30:00
-- In a sentence, 46800 is the number of seconds at 11:30:00, and 2100 is the number of seconds at 30 minutes.
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
Result:
Emyeeid time0 time1 Iodate
--------------------------------------------------------------------------------
1001 11:44:09. 000 11:35:47. 000
1002 11:36:07. 000 11:50:36. 000
(2 rows affected)
Key Skills:
-- Insert all off-duty data records, time period 11:30:00 -- 12:00:00
-- In a sentence, 41400 is the number of seconds at 11:30:00, and 1800 is the number of seconds at 30 minutes.
Dateadd (second, 41400 + ABS (checksum (newid () % 1800, iodate) time1,
Reference here: http://topic.csdn.net/u/20071112/09/f1e09827-2f17-44fe-b70b-533b5afc1c66.html
This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/zhou__zhou/archive/2007/11/13/1881455.aspx