[SQL] Stored ProcedureTo Generate Random Time of Day

sql 0 comments suggest edit

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]

CREATE PROCEDURE [dbo].GetRandomTimeOfDay
    @timeOfDay int = 0 OUTPUT
    , @maxHour int = 24 -- Upper bound for the hour.
    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) )

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

You can download the SQL in a zip file

Found a typo or error? Suggest an edit! If accepted, your contribution is listed automatically here.



4 responses

  1. Avatar for cheese frenzy
    cheese frenzy June 24th, 2007

    nice one! much appreciated :)

  2. Avatar for towps
    towps November 25th, 2008

    select DATEADD(second, rand()*86400, DATEADD(d, rand()*(CAST((@date_to - @date_from) AS INT )), @date_from))

  3. Avatar for Jason
    Jason February 13th, 2011

    This can actually be used in lots of different situations. Thanks for posting.
    Jason, the iPhone developer

  4. Avatar for Mike Emo
    Mike Emo August 30th, 2012

    Here was an approach I took. I need to be able to specify the range (a random time between 5 am and 7 pm for example).
    -- I want to get a randome time of day between 12 am and 1:30 am
    DECLARE @startTime Time = '00:00:00'
    DECLARE @endTime TIME = '01:30:00'
    -- Get the number of seconds between these two times
    -- (eg. there are 3600 seconds between 12 AM and 1 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