How to generate random dates in a range?

[richc]

Hi,

I'm a bit stuck trying to generate random dates in the format 'yyyy-MM-dd' (e.g. '2013-12-31') for a varchar(10) column. I wonder if anyone can advise me on how to do this.

Firstly, am I right in thinking there is no date generator in this product? I couldn't find one. If I have missed it that could be an easy solution!

Anyway, I attempted to do it in a Python script:

Code: Select all

import datetime
import random

def main(config):  
    random.seed(config["seed"])
    some_date = random_date()	
    return some_date
		
def random_date():
    earliest = datetime.date(1910,1,1)
    latest  = datetime.date(2018,1,1)
    delta = latest - earliest
    int_delta = (delta.days * 24 * 60 * 60) + delta.seconds    
    random_second = random.randrange(int_delta)
    return earliest + datetime.timedelta(seconds = random_second)
	

The problem is that this generates the same date over and over again. I'm guessing a new instance of the script is run for each row, and the random number generator is re-seeded so I never go beyond the first item in the pseudo-random sequence.

So, how can I get this to deliver different random dates for each row?

Any help would be much appreciated! Thank you.

[.jp]

Hello,

Thanks for the post.

You should use the following code:

Code: Select all

import random
import clr

random.seed(config["seed"])
startDate = DateTime(1910,1,1)

def main(config):
  while True:
    # Add number of days to a startDate
    yield startDate.AddDays(random.randint(1, 100000)).ToString('yyyy-MM-dd');

Or the following one:

Code: Select all

import random
import clr

random.seed(config["seed"])
startDate = DateTime(1910,1,1)
endDate = DateTime(2018,1,1)
daysToAdd = (endDate - startDate).Days

def main(config):
  while True:
   # Add number of days to a startDate
   yield startDate.AddDays(random.randint(1, daysToAdd)).ToString('yyyy-MM-dd')

Also you would use RegExp generator instead of Python:

Code: Select all

(19[1-9][0-9]|20(0[0-9]|1[0-8]))-((0[0-9])|(1[0-2]))-(0[1-9]|(1[0-9]|2[0-8]))

Best Regards.