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