1

Well i have to update a table called "patron_filtrado" and fill one column called "fecha_nacimiento" type date. With random dates, the dates will be in a range between 1920 and 1980.

i thought on something like this

Update patron_filtrado
  SET fecha_nacimiento= DateAdd(d, ROUND(DateDiff(d, '1920-01-01', '2080-12-31') * RAND(CHECKSUM(NEWID())), 0),
  DATEADD(second,CHECKSUM(NEWID())%48000, '1920-01-01'))

im ok?

Magnum
  • 1,555
  • 4
  • 18
  • 39
José Pablo Madrigal Rodríguez
  • 51
  • 8

1 Answers1

0

Modified from: How to update rows with a random date

NEWID is better then trying to use RAND: RAND does not generate different values row in a single SELECT or UPDATE (well it didn't in SQL 2000, in case behaviour has changed).

Try

SELECT DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 22265), '1920-01-01')
FROM your_table

This generates dates starting from 1920-01-01 through 1980-12-31.

If you want to update, then

UPDATE your_table
SET your_date_field = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 22265), '1920-01-01')
WHERE your_conditions
Community
  • 1
  • 1
Magnum
  • 1,555
  • 4
  • 18
  • 39
  • it say this "Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(NEWID()) % 22265), '1920-01-01')' at line 1 " – José Pablo Madrigal Rodríguez Sep 15 '14 at 23:24
  • Line 1 = UPDATE your_table? Did you update it to UPDATE patron_filtrado correct? Modify the above statement to fit into your MySQL version and syntax. – Magnum Sep 15 '14 at 23:29
  • UPDATE padron_filtrado SET fecha_nacimiento = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 22265), '1920-01-01'); i tried this – José Pablo Madrigal Rodríguez Sep 15 '14 at 23:38