Menu
I have a simple SQL table which has a DateTime column. I would like to update all the rows (>100000 rows) with a random date. Is there a simple way to do this a SQL Query?
814
In this case ( DATEDIFF(DAY,@start,@end ), the difference between the start date and end date will be obtained in days. By adding this value to start date, you can generate random dates between the start date and end date. However, this will not return the end date ( 1980-01-05 ) as a randomly generated date.
You can use now() with default auto fill and current date and time for this. Later, you can extract the date part using date() function. Let us set the default value with some date.
Use this to generate a smalldatetime between 01 Jan 1900 and 06 Jun 2079 (not checked, SQL not installed)
DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0) 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).
Edit: like this
UPDATE table SET datetimecol = DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0) Edit: changed 65535 to 65530 and added ABS to avoid overflow at upper limit of range
96
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
