StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I have write to you this simple function that returns a random date between date range:</p> <pre><code>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 </code></pre> <p>to call the function:</p> <pre><code>select dbo.date_rand( '1/1/2001', '10/1/2001' ) </code></pre> <p>you can combine function with a row generator:</p> <pre><code>;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 </code></pre> <p><strong>EDITED</strong></p> <p>To generate random numbers use:</p> <pre><code>RAND(CHECKSUM(NEWID())) </code></pre> <p>instead of RAND()</p> <p><strong>EDITED II</strong></p> <p>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().</p> <p>A workaround is to <a href="http://www.sqlpointers.com/2006/07/accessing-non-deterministic-functions.html" rel="nofollow">create a view like</a>:</p> <pre><code>create view myRandomNumber as select cast( RAND(CHECKSUM(NEWID()))*1000 as int) as new_rand </code></pre> <p>and then use it in function:</p> <pre><code>... select @days_rand = new_rand % @days_between from myRandomNumber ... </code></pre> <p>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.</p> <pre><code>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 </code></pre> <p>You can <a href="http://data.stackexchange.com/stackoverflow/query/63654/new" rel="nofollow">test here this query</a>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload