...and you like it

[SQL] Stored ProcedureTo Generate Random Time of Day

Here is a function that will generate a random time of day. Later I will
show why I am posting this particular query and how I am using it. It
comes in useful when trying create random scheduled jobs in SQL Server.
I made use of a technique for generating random
dates
via Jon
Galloway.

Parameter | DataType | Extra | Description
————|———-|————-|————
@timeOfDay |int |OUTPUT | Represents a time of day using the format HHMMSS. For example, midnight is represented as 0 and 1:52:01 PM is represented as 135201.
@maxHour |int |Default = 24 | Upper bound for the hour of the day. So to generate a random time of day
between midnight and 3 AM, specify 3.

Here is an example of its usage.

-- Generate time of day between midnight and 3DECLARE@timeOfDayintexecGetRandomTimeOfDay@timeOfDayOUTPUT,24PRINT@timeOfDay

And here is the stored procedure declaration itself. Notice I am
creating this procedure within the master database.

USE[master]GOCREATEPROCEDURE[dbo].GetRandomTimeOfDay(@timeOfDayint=0OUTPUT,@maxHourint=24-- Upper bound for the hour.)ASBEGINIF@maxHour>24OR@maxHour<1RAISERROR('Choose value between 1 and 24',16,1)DECLARE@randomHoursintSELECT@randomHours=(@maxHour-1)*RAND(CAST(CAST(newid()asbinary(8))asINT))DECLARE@randomMinutesintSELECT@randomMinutes=60*RAND(CAST(CAST(newid()asbinary(8))asINT))DECLARE@timeOfDayDateDateTimeSET@timeOfDayDate='00:00:00'SET@timeOfDayDate=DATEADD(hh,@randomHours,@timeOfDayDate)SET@timeOfDayDate=DATEADD(mi,@randomMinutes,@timeOfDayDate)DECLARE@timeAsStringvarchar(8)DECLARE@timeWithoutColonsvarchar(6)SET@timeAsString=CONVERT(varchar(8),@timeOfDayDate,8)SET@timeWithoutColons=REPLACE(@timeAsString,':','')SET@timeOfDay=(CAST(@timeWithoutColonsasint))ENDGO