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

[mwallman]

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:

LOAD

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?

[sunny_talwar]

May be like this:

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

[swuehl]

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.

[sfatoux72]

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

[swuehl]

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 ?

[sunny_talwar]

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.

[johnw]

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.

[johnw]

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.

[johnw]

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.

[sunny_talwar]

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!