SQL Feather and Quill

So apparently I like scripts that create random data.  I’ve written a couple of posts that create random numbers and random characters.  This week I was working on a clean up job that would delete data that was over 50 days old.  To test my clean up routine, I had to combine the random number script with the DATEADD function – here’s the basis for it:

Select Dateadd(D, (-1)* ROUND((RAND() * (100)),0), Getdate())

Breaking it down, the ROUND((RAND() * (100)),0) section randomly chooses an integer from 0 to 100, then multiplies that by –1 as we want to go back in time.  Finally the DATEADD function adds the negative random integer number of days to today’s date.

Here’s an example that populates a test table with 1000 rows of data:

SET NOCOUNT ON
Go
Declare @RandomDates Table (DateTest datetime)
Declare @Counter int
Set @Counter = 0
While @Counter < 1000
Begin
    Insert @RandomDates (DateTest)
    Select Dateadd(D, (-1)* ROUND((RAND() * (100)),0), Getdate()) --Random Date 0 to 100 days old
    Set @Counter = @Counter + 1
End

Select Datediff(d,DateTest, getdate()) as DaysOld,
    Count(Datediff(d,DateTest, getdate())) as Count
from @RandomDates
Group by Datediff(d,DateTest, getdate())
Order by DaysOld Desc