[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 3
DECLARE @timeOfDay int
exec GetRandomTimeOfDay @timeOfDay OUTPUT, 24
PRINT @timeOfDay

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

USE [master]
GO

CREATE PROCEDURE [dbo].GetRandomTimeOfDay
( 
    @timeOfDay int = 0 OUTPUT
    , @maxHour int = 24 -- Upper bound for the hour.
)
AS
BEGIN
    IF @maxHour > 24 OR @maxHour < 1
        RAISERROR ('Choose value between 1 and 24', 16, 1)   
    
    DECLARE @randomHours int
    SELECT @randomHours = 
        (@maxHour - 1) * 
RAND(CAST(CAST(newid() as binary(8)) as INT))
    
    DECLARE @randomMinutes int
    SELECT @randomMinutes = 
        60 * RAND(CAST(CAST(newid() as binary(8)) as INT))
    
    DECLARE @timeOfDayDate DateTime
    SET @timeOfDayDate = '00:00:00'
    
    SET @timeOfDayDate = 
DATEADD(hh, @randomHours, @timeOfDayDate)
    SET @timeOfDayDate = 
DATEADD(mi, @randomMinutes, @timeOfDayDate)
    
    DECLARE @timeAsString varchar(8)
    DECLARE @timeWithoutColons varchar(6)
    
    SET @timeAsString = CONVERT(varchar(8), @timeOfDayDate, 8)
    SET @timeWithoutColons = REPLACE(@timeAsString, ':', '')
    
    SET @timeOfDay = ( CAST(@timeWithoutColons as int) )
END
GO

In a later installment, I will show you why this is useful.

You can download the SQL in a zip file