SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

Sql Server: Get Random Time in given range : Function

on April 1, 2013
--Requirement to get a random time of day between @startTime and @endTime
Create Function dbo.GetRandomTimeInRange
(
@startTime Time = '00:00:00',
@endTime TIME = '01:30:00'
)
-- Select dbo.GetRandomTimeInRange ('23:00:00', '02:01:00')
RETURNS Time
BEGIN

	-- Get the number of seconds between the specified time
	-- (eg. there are 5400 seconds between 12 AM and 1:30 AM)
	DECLARE @MaxSec int = DATEDIFF(ss, @startTime, @endTime)
	-- Get a random number of seconds between 0 and the number of 
	-- seconds between @startTime and @endTime (@maxSeconds)	
	 DECLARE @randomSec int = (@MaxSec + 1) * 
	  (DATEPART(Ms, Getdate())/1000.0)
	/* Add the random number of seconds to @startTime and 
	return that random time of day  */
	/* In the above code Milli Seconds is divided by 1000 to make the 
	value < 1 or in other words extreact a few percent of @randomSec 
	so as to get the value less than @endTime 
	 */
	RETURN (convert(Time, DateAdd(second, @randomSec, @startTime))) 

END
GO

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: