October 17, 2007 at 3:44 pm
Rough guess based on what you described above.
Step 1: Generate a random number between 1 and 10000
Step 2: Output random number in step 1 + DATEDIFF(DAY, '1/1/1800', dateColumnInTable).
If they're doing statistical analysis, on this, they'll only care that things that happened on the same day have the same number (which they will for each run, but not between runs).
If they really want the dates, just not in a layman-readable format, don't bother. From experience, you wan't be able to come up with any algorythm that they'll be able to use without being cumbersome that someone else couldn't figure out easily enough. If this case, just drop on the real dates and control the paper output. Anything else (as Matt said above), just isn't worth the bother.
Again, all depends on why the want the specific date. The random number approach will let them do things like "on a given day", "within x number of days of each other" and other similar type analyses. They'll just never be able to get the real dates out of it.
Good luck with it.
October 18, 2007 at 1:12 pm
use a Julian calendar instead of a traditional one
October 19, 2007 at 12:35 pm
thanks for the contribution
October 31, 2007 at 11:19 am
How do I write a function to generate a random number
October 31, 2007 at 11:41 am
There's already a system function for that: RAND().
A few things about rand():
- RAND() will generate a random number between 0 and 1, so if you want "bigger numbers, just multiply by the upper bound.
- RAND() with no parameters will generate ONCE per batch, so
Select rand() as randvalue, a.col1 from a
would return the SAME value in randvalue in every row. To get a unique value for each record, try something like:
rand(cast(newid() as varbinary))
So...if you wanted random values between 100 and 250, you could use
Select cast(rand(cast(newid() as varbinary))*150+100 as integer) ....
If you don't mind an alphanumeric random value - you could always use the NEWID() function instead.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 31, 2007 at 1:08 pm
I know this is a little late for the bulk of the discussion, but couldn't you just create a table with one column for the actual date and another in an altered format (using whatever algorithm you like) ... anyone with access to the database can figure out the real date and it will be consistent across reports / projects etc.
November 1, 2007 at 11:33 am
Thanks for the input
November 1, 2007 at 11:34 am
Thanks for the input
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply