循环插入一定时间范围内的随机日期值
SQL Server中循环插入一定时间范围内的随机日期值
-- First, let's declare the date range. I am declaring this
-- here for the demo, but this could be done anyway you like.
DECLARE @date_from DATETIME;
DECLARE @date_to DATETIME;
-- Set the start and date dates. In this case, we are using
-- the month of october, 2006.
SET @date_from = '2006-10-01';
SET @date_to = '2006-10-30';
-- insert random dates.
insert into randomdate SELECT
(
-- Remember, we want to add a random number to the
-- start date. In SQL we can add days (as integers)
-- to a date to increase the actually date/time
-- object value.
@date_from +
(
-- This will force our random number to be GTE 0.
ABS(
-- This will give us a HUGE random number that
-- might be negative or positive.
CAST(
CAST( NewID() AS BINARY(8) )
AS INT
)
)
-- Our random number might be HUGE. We can't have
-- exceed the date range that we are given.
-- Therefore, we have to take the modulus of the
-- date range difference. This will give us between
-- zero and one less than the date range.
%
-- To get the number of days in the date range, we
-- can simply substrate the start date from the
-- end date. At this point though, we have to cast
-- to INT as SQL will not make any automatic
-- conversions for us.
CAST(
(@date_to - @date_from)
AS INT
)
)
)
