SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

Get Random Time in a given range: Sql Server

on April 1, 2013
DECLARE @startTime Time = '01:00:00'
DECLARE @endTime TIME = '02:30:00'
-- Get the number of seconds between these two times
-- (eg. there are 5400 seconds between 1 AM and 2.30 AM)
DECLARE @maxSeconds int = DATEDIFF(ss, @startTime, @endTime)
-- Get a random number of seconds between 0 and the number of 
-- seconds between @startTime and @endTime (@maxSeconds)
DECLARE @randomSeconds int = (@maxSeconds + 1) 
* RAND(convert(varbinary, newId() )) 
-- Add the random number of seconds to @startTime and return that random time of day
SELECT (convert(Time, DateAdd(second, @randomSeconds, @startTime))) 
AS RandomTime

--Note: Above code cannot be used inside the function as it would give following errors
--Invalid use of a side-effecting operator 'newid' within a function.
--Invalid use of a side-effecting operator 'rand' within a function.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: