Here’s a quick bit of code that I find useful for generating test data for dates within a given date range. It uses a combination of the rand, newid, checksum, datediif and dateadd functions. First we calculate the checksum on a newid(). This generates a random integer value. Next we apply the rand function to the results and generate a random fraction which is applied to the datediff of your date range + 1. This results in a random number that represents the number of days to add to the start date.
dateadd(day, rand(checksum(newid()))*(1+datediff(day, ‘2011-01-01′,’2014-04-30’)), ‘2011-01-01’)
DECLARE @StartDate Datetime = ‘2011-01-01’ DECLARE @EndDate Datetime = ‘2014-04-30’ SELECT DATEADD(day,RAND(CHECKSUM(NEWID()))*(1+DATEDIFF(day, @StartDate,@EndDate)),@StartDate)