I am new to sql server. I need to generate random dates selected from a given date range.
Like the date of employment of an employee should be anywhere between 2011-01-01 and 2011-12-31. The generated dates should be inserted into a 1000 row table randomly.
Can any one guide me with my query?
declare @FromDate date = '2011-01-01'
declare @ToDate date = '2011-12-31'
select dateadd(day,
rand(checksum(newid()))*(1+datediff(day, @FromDate, @ToDate)),
@FromDate)
You can simply use this query.
DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 364 ), '2011-01-01')
If you want to add dates from a different timeline, you can change 01-01-2011 and 364. 364 is equal to days you want to add. In this case, it's between 01-01-2011 and 31-12-2011.
(31-12-2011 is also included.)
For example, let say you want to add a random date between 2018-01-01 and 2018-01-31, you can change the query like this.
DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 31), '2018-01-01')
To insert (one row/date), simply use this...
DECLARE @rdate DATE = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 31), '2018-01-01')
INSERT INTO TableName ([DateColumn])
VALUES (@rdate);
Output
+-----+------------+
| ID | DateColumn |
+-----+------------+
| 01 | 2018-01-21 |
+-----+------------+
Online Demo: SQLFiddle.com
To insert 1000 rows at once...
DECLARE @rdate DATE
DECLARE @startLoopID INT = 1
DECLARE @endLoopID INT = 1000 -- Rows you want to add
WHILE @startLoopID <= @endLoopID
BEGIN
SET @rdate = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 364 ), '2011-01-01');
SET @startLoopID = @startLoopID + 1;
INSERT INTO TableName ([DateColumn])
VALUES (@rdate);
END
Output
+--------+------------+
| ID | DateColumn |
+--------+------------+
| 10000 | 2010-04-07 |
| 10001 | 2010-07-29 |
| 10002 | 2010-11-18 |
| 10003 | 2010-05-27 |
| 10004 | 2010-01-31 |
| 10005 | 2010-08-26 |
| ˅ | ˅ |
| 20000 | 2010-06-26 |
+--------+------------+
Online Demo: SQLFiddle.com
To update existing rows...
UPDATE TableName
SET [DateColumn] = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 364 ), '2011-01-01')
WHERE condition;
Online Demo: SQLFiddle.com
I have write to you this simple function that returns a random date between date range:
create function date_rand ( @fromDate date, @toDate date) returns date
as
begin
declare @days_between int
declare @days_rand int
set @days_between = datediff(day,@fromDate,@toDate)
set @days_rand = cast(RAND()*10000 as int) % @days_between
return dateadd( day, @days_rand, @fromDate )
end
to call the function:
select dbo.date_rand( '1/1/2001', '10/1/2001' )
you can combine function with a row generator:
;WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
SELECT dbo.date_rand( '1/1/2001', '10/1/2001' )
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D ( n )
WHERE n <= 1000
EDITED
To generate random numbers use:
RAND(CHECKSUM(NEWID()))
instead of RAND()
EDITED II
Function returns 'Invalid use of a side-effecting operator 'rand' within a function' error. THis is because we can not use non-deterministic functions like RAND() or NEWID().
A workaround is to create a view like:
create view myRandomNumber as
select cast( RAND(CHECKSUM(NEWID()))*1000 as int) as new_rand
and then use it in function:
...
select @days_rand = new_rand % @days_between from myRandomNumber
...
or simple don't use the function and write expresion on select. I have write a function only yo explain step by step the solucion.
declare @fromdate date
declare @todate date
set @fromdate = '1/1/2001'
set @todate = '10/1/2001'
;WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
SELECT
dateadd( day,
cast( RAND(CHECKSUM(NEWID()))*1000 as int) %
datediff(day,@fromDate,@toDate),
@fromDate )
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D ( n )
WHERE n <= 1000
You can test here this query.
Well, I know that this is an old question, but it was linked from a newer one so... Here are my 2 cents:
Based on these premises, I believe that there really is no actual need to store a 1000 random dates in a table, when it's possible to store only the relevant date and just select how many rows and in whatever order you need.
First, store the data inside the table. you can use a Tally table to create the relevant date range.
A Tally table is a table containing a sequence of numbers. for argument's sake, let's assume you already created your tally table of numbers between 0 to 1,000,000.
You can check this link for the best way to create one, personally I like this method:
-- create the tally table
SELECT TOP 100000 IDENTITY (int ,0, 1) as num
INTO Tally
FROM sys.sysobjects
CROSS JOIN sys.all_columns
Now that you have the Tally table, it's fairly simple to create a calendar:
DECLARE @FromDate datetime = GETDATE(),
@ToDate datetime = DATEADD(YEAR, 1, GETDATE()) -- a year from now in my example
;With CalendarCTE AS
(
SELECT DATEADD(DAY, num, @FromDate) As caneldarDate
FROM Tally
WHERE num < DATEDIFF(DAY, @FromDate, @ToDate)
)
Now that you have the calendar and the tally table, it's fairly simple to use them both to get any number of records in any order you want. A thousand randomly-ordered dates? no problem:
SELECT TOP 1000 caneldarDate
FROM CalendarCTE c
CROSS JOIN Tally t
WHERE t.num < 1000
ORDER BY NEWID()
Full script, including creating and dropping the tally table took less then a second to execute:
-- create the tally table
SELECT TOP 100000 IDENTITY (int ,0, 1) as num
INTO Tally
FROM sys.sysobjects
CROSS JOIN sys.all_columns
-- crealte the calendar cte:
DECLARE @FromDate datetime = GETDATE(),
@ToDate datetime = DATEADD(YEAR, 1, GETDATE())
;With CalendarCTE AS
(
SELECT DATEADD(DAY, num, @FromDate) As caneldarDate
FROM Tally
WHERE num < DATEDIFF(DAY, @FromDate, @ToDate)
)
-- select a 1000 random dates
SELECT TOP 1000 caneldarDate
FROM CalendarCTE c
CROSS JOIN Tally t
WHERE t.num < 1000
ORDER BY NEWID()
-- cleanup
DROP TABLE Tally
