Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1148
  • Last Modified:

How to update table using a random date

Hi!
I need to update a date column in a table with random dates. I've tried the script below, but it inserts the same date into all the fields. The DATEADD part on its own generates random dates just fine, and if you stick it in a loop it generates lots of different dates, but it doesn't work when run as part of an UPDATE statement.

Does anyone know how to fix this?

SQL Being used >>>>>>>>>>>>>>>>>

Update KT8
Set SubDate = DATEADD(second, rand()*36000, DATEADD(day, rand()*120, '2008-01-01 08:00:00'))


Thanks in advance!

Jason
0
jasonhick70
Asked:
jasonhick70
  • 2
1 Solution
 
jamesguCommented:
you may have to use NEWID() to set a random value for each row
0
 
BrandonGalderisiCommented:
rand() will be unique per batch.  So if it's run for 1000 records, it will be the same for all.  Do you have a surrogate key (ID field) such as an identity?  You can use that in conjunction with your rand()*36000 to help make it a LITTLE more randonm.

something like :

Update KT8
Set SubDate = DATEADD(second, rand(id)*36000, DATEADD(day, rand()*120, '2008-01-01 08:00:00'))
0
 
jasonhick70Author Commented:
Hi! Thanks for your response guys.

The rand(id) does mix things up a little bit, but I was kinda looking for something MORE random! newid() sounds like it might do the job, but i'm not sure how to implement it.... just a newbie. Any pointers?
0
 
BrandonGalderisiCommented:
The problem is that rand() returns a decimal value between 0 and 1.  id only helps to give different values for each rand() per row but since you are adding only between 36000 and 0, you will only have a possible variance of 10 hours between your min and max date selected.  If you increase the 36000 to something much higher like 1 million (11 day span), or add that to minutes  and not seconds (25 day span) you will get more SEEMINGLY random data.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now