17 Replies Latest reply: May 19, 2016 5:45 PM by John Witherspoon

# How to Generate random dates between a Start Date and End Date variables?

Hello all,

I have two variables where users can input a start date and end date.

Let's called it:

vStartDate

vEndDate

Then I may have a table like this:

Payments:

PaymentID,

<Create payment date based on vStartDatr and vEndDate here> PaymentDate,

CustomerID,

ProductID

AutoGenerate 300;

How can I get the PaymentDate to have a date randomly generated from the vStartDate and vEndDate?

• ###### Re: How to Generate random dates between a Start Date and End Date variables?

May be like this:

Date(\$(vStartDate) + Ceil(Rand() * (\$(vEndDate) - \$(vStartDate)))) as PaymentDate

• ###### Re: How to Generate random dates between a Start Date and End Date variables?

To get an equal chance to create dates in your range, try something like

Date(\$(vStartDate)+ Round(Rand() * (\$(vEndDate) - \$(vStartDate)+1)-0.5)) as PaymentDate

and take care that your variables expand to a numeric representation.

• ###### Re: How to Generate random dates between a Start Date and End Date variables?

Hi Swuehl,

It's a good thing to get the same chance for extremity date (vStartDate, vEndDate) than for others dates to be selected. But you will have a problem if Rand() return 1, you will have vEndDate+1 returned...

You need to modify the expression like that:

Date(\$(vStartDate)+ Round(Rand() * (\$(vEndDate) - \$(vStartDate)+0.999)-0.5)) as PaymentDate

• ###### Re: How to Generate random dates between a Start Date and End Date variables?

Actually, I don't really know if the RAND() function returns values in the range [0,1] or ]0,1[ or a half - open variant.

HIC once mentioned [0,1] in one of his blog post but the HELP just states

The function returns a random number between 0 and 1

Anyone ever seen an integer 1 returned ?

• ###### Re: How to Generate random dates between a Start Date and End Date variables?

On a AutoGenerate of 100M, the max Rand() * 10000000000 I got was 9999999986.0302

Table:

LOAD Rand() * 10000000000 as Num

AutoGenerate 100000000;

So I guess Stefan might be right.

• ###### Re: How to Generate random dates between a Start Date and End Date variables?

You'd have to run FAR more than 100M to have any sort of statistical certainty that 1 is being excluded, and are likely to have to run FAR more than 100M to see if 0 is included. I suppose that's an argument for "it doesn't matter". Your chance of ever seeing exactly 0 or 1, even if they're included, is extremely remote. Well, assuming actual randomness, which isn't the case. Hard to know with pseudo-random numbers. But I hate "this is statistically unlikely to cause you a problem" as an answer.

• ###### Re: How to Generate random dates between a Start Date and End Date variables?

I wish had the computing power to go to a level where the test might give more conclusive results. But just based on a 'SINGLE SMALL' sample test I deduced that Stefan is right.

Am I being a good statistician? No.

Am I being a good friend? Hell Ya!

• ###### Re: How to Generate random dates between a Start Date and End Date variables?

I tend to trust HIC, but [0,1] strikes me as very strange. I would expect [0,1) as we get with Excel and Java.

• ###### Re: How to Generate random dates between a Start Date and End Date variables?

Here's a possible argument that it's likely [0,1). We have 52 bits to play with in IEEE 754 double-precision binary floating point, and it would be very rational to interpret that as a number between 0.00000... and 0.11111... binary, which would be [0,1). You could go [0,1], but you'd have to lose one of those random bits to do so, so you'd have only half as many random numbers.

• ###### Re: How to Generate random dates between a Start Date and End Date variables?

I was somehow hoping that John would jump in

Maybe we should subtract a small number like Sebastien suggested to be on the safe side, or maybe we could use something like

Date(

RangeMin(

RangeMax(

\$(vStartDate)+ Round(Rand() * (\$(vEndDate) - \$(vStartDate)+1)-0.5)

,\$(vStartDate)

)

,\$(vEndDate)

)

) as PaymentDate

This should be super-safe, shouldn't it?

• ###### Re: How to Generate random dates between a Start Date and End Date variables?

I'd personally let the possible 1 slide. I suspect the function returns [0,1). And if I'm wrong, I believe there's a 1/(2^52) chance of getting 1. I can live with that. I like to floor() my rand() since I believe in [0,1), so my variation on the theme would be:

date(\$(vStartDate)+floor(rand()*(\$(vEndDate)-\$(vStartDate)+1))) as PaymentDate

Say we're May 1 to May 10 of 2016.

0.0 - <0.1 : May 1
0.1 - <0.2 : May 2
...
0.9 - <1.0 : May 10
1 exactly  : May 11 (may not be possible, is at least highly improbable)

Looks like an equal chance of each date.

EDIT: 1/(2^51+1) chance based on a guess of how they'd tackle including 1 by eliminating one of the random bits

• ###### Re: How to Generate random dates between a Start Date and End Date variables?

Would using a Ceil without adding the one a not so good option here?

Date(\$(vStartDate) + Ceil(Rand() * (\$(vEndDate) - \$(vStartDate)))) as PaymentDate

UPDATE: Or I should say equal to Floor?

• ###### Re: How to Generate random dates between a Start Date and End Date variables?

I believe that makes it virtually impossible to get vStartDate. You're randomly distributed between the other dates, so that much is good.

UPDATE: Using floor() isn't why I add 1. I add 1 because I need to randomly distribute between the right number of dates. May 10 - May 1 = 9 days, but I need 10 days.

• ###### Re: How to Generate random dates between a Start Date and End Date variables?

Alright, I need to go back and test what you just mentioned. To me both looked the same , but I might be mistaken and won't realize the difference until I run both the options.

Thanks

• ###### Re: How to Generate random dates between a Start Date and End Date variables?

Let us know. I HATE it when people tell me "that won't work because" without testing it, and I totally just did that to you. Even if I turn out to be right, sorry!

• ###### Re: How to Generate random dates between a Start Date and End Date variables?

I don't HATE it John, gives me an option to try, test and learn a new thing. If would have told me that, Yes its the same, I would have trusted you and would have never went back and tested it at my end. But this give me an opportunity to go back and test and learn it

• ###### Re: How to Generate random dates between a Start Date and End Date variables?

I almost did it to someone else a few weeks back. It might have been Stephan. Someone wrote an expression that I "knew" was wrong, and I almost said so without testing it, but then I got smart and tested it first. It worked, and once I saw it work, I understood why it worked. Almost put my foot in my mouth. Learned something instead.

Moral of the story: Testing good! Jumping to conclusions bad!