Randomise Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Randomise Date

Author

Topic

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-19 : 09:21:40

I have a Arrival date of 01/01/2012 the Departure date can be any date between 02/01/2012 and 29/01/2012 and needs to added to the database either via an Update/Insert Statement. Can you help with the code.

SELECT ArrivalDate, DATEADD(day, RAND(checksum(NEWID()))
* LengthOfStay.LengthofStay, ArrivalDate) AS DepartureDate
FROM Bookings, LengthOfStay
ORDER BY ArrivalDate

But the Departure date cannot be the same as the arrival date.

Thanks

Wayne

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-19 : 11:49:43

Are you trying to randomize the date of departure with the constraint that it be between January 3 and January 29? Or, are you asking how to insert it into another table? If you are trying to randomize:

DATEADD( day, 1 + 26*RAND(checksum(NEWID())),'1/2/2012')

If you are trying to update a column, the code would be something like this:

UPDATE tbl SET 
	DepartureDate = DATEADD( day, 1 + (LengthofStay-1)*RAND(checksum(NEWID())),ArrivalDate)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources