How to update rows with a random date

by

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

More Related Contents:

  1. Sql Mathematical calculations [closed]
  2. How to display data in JOIN, and return null value? [closed]
  3. How to create a SQL Server function to “join” multiple rows from a subquery into a single delimited field? [duplicate]
  4. Query to get only numbers from a string
  5. How to deal with SQL column names that look like SQL keywords?
  6. Truncate (not round) decimal places in SQL Server
  7. SQL Server indexes – ascending or descending, what difference does it make?
  8. Identity column value suddenly jumps to 1001 in sql server [duplicate]
  9. Dynamic Pivot Columns in SQL Server
  10. Is it possible to select sql server data using column ordinal position
  11. Selecting N rows in SQL Server
  12. A SQL Query to select a string between two known strings
  13. How can I INSERT data into two tables simultaneously in SQL Server?
  14. SQL “select where not in subquery” returns no results
  15. Dynamic SQL to generate column names?
  16. Column name or number of supplied values does not match table definition
  17. SQL Server PIVOT perhaps?
  18. In SQL Server how to Pivot for multiple columns
  19. How to find consecutive rows based on the value of a column?
  20. SQL Group BY, Top N Items for each Group
  21. Which SQL query is faster? Filter on Join criteria or Where clause?
  22. Could not find stored procedure ‘dbo.aspnet_CheckSchemaVersion’
  23. SQL Query – Concatenating Results into One String [duplicate]
  24. Slow bulk insert for table with many indexes
  25. operation not allowed when the object is closed when running more advanced query
  26. How do I list all the columns in a table?
  27. SQL Server 2005 – Export table programmatically (run a .sql file to rebuild it)
  28. Convert bigint to datetime
  29. SQL SERVER DATETIME FORMAT
  30. Insert all values of a table into another table in SQL

Leave a Comment