Post reply

Date encrytion

  • October 17, 2007 at 3:44 pm

    #742904

    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.

    Greg Walker
    DBA, ExpenseWatch.com

  • October 18, 2007 at 1:12 pm

    #743266

    use a Julian calendar instead of a traditional one

  • October 19, 2007 at 12:35 pm

    #743758

    thanks for the contribution

  • October 31, 2007 at 11:19 am

    #747185

    How do I write a function to generate a random number

  • October 31, 2007 at 11:41 am

    #747192

    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

    #747222

    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

    #747477

    Thanks for the input

  • November 1, 2007 at 11:34 am

    #747478

    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