Distinct random time generation in the fixed inter

Question

I'm trying to generate a random time between 8:00 AM and 8:00 PM for each row that is selected from a data set, however, I always get the same random value for each row – I want it to be different for each row.

Table schema & data:

╔══════╦════════════════╗
║  ID  ║  CREATED_DATE  ║
╠══════╬════════════════╣
║ ID/1 ║   26/04/2014   ║
║ ID/2 ║   26/04/2014   ║
║ ID/3 ║   26/04/2014   ║
║ ID/4 ║   26/04/2014   ║
║ ID/5 ║   26/04/2014   ║
╚══════╩════════════════╝

Сurrent SQL statement:

SELECT [ID]
     , MyFunction.dbo.AddWorkDays(14, [CREATED_DATE]) AS [New Date]
     , CONVERT(VARCHAR, DATEADD(MILLISECOND, CAST(43200000 * RAND() AS INT), CONVERT(TIME, '08:00')), 114) AS [New Time]
FROM [RandomTable]

Current results (same time for each row in the [New Time] column):

╔══════╦════════════════╦════════════════╗
║  ID  ║    New Date    ║    New Time    ║
╠══════╬════════════════╬════════════════╣
║ ID/1 ║   10/05/2014   ║    09:41:43    ║
║ ID/2 ║   10/05/2014   ║    09:41:43    ║
║ ID/3 ║   10/05/2014   ║    09:41:43    ║
║ ID/4 ║   10/05/2014   ║    09:41:43    ║
║ ID/5 ║   10/05/2014   ║    09:41:43    ║
╚══════╩════════════════╩════════════════╝

Desired results (different time for each row in the [New Time] column):

╔══════╦════════════════╦════════════════╗
║  ID  ║    New Date    ║    New Time    ║
╠══════╬════════════════╬════════════════╣
║ ID/1 ║   10/05/2014   ║    09:41:43    ║
║ ID/2 ║   10/05/2014   ║    15:05:23    ║
║ ID/3 ║   10/05/2014   ║    10:01:05    ║
║ ID/4 ║   10/05/2014   ║    19:32:45    ║
║ ID/5 ║   10/05/2014   ║    08:43:15    ║
╚══════╩════════════════╩════════════════╝

Any ideas on how to fix this? All of the above is just sample data – my real table has around 2800 records (not sure if that will make a difference to anyone's suggestions).

Answer 1

Here's another option that gives you a bit more control over how the time is generated. You can specify the interval between the random times. It also doesn't make use of the RAND function.

DECLARE @StartTime  VARCHAR(10) = '08:00',
        @EndTime    VARCHAR(10) = '20:00',
        @Interval   INT = 5 --(In Seconds)

WITH times AS(
    SELECT CONVERT(TIME, @StartTime) AS t
    UNION ALL
    SELECT DATEADD(SECOND, @Interval, t)
    FROM times
    WHERE t < @EndTime
)

SELECT *, 
(SELECT TOP 1 t FROM times WHERE d.Id > 0 ORDER BY NEWID())
FROM #data d
option (maxrecursion 0)

On a side note :
If you remove the WHERE clause in the subquery above (WHERE d.Id > 0), the same time value is returned for all the rows, i.e. the same problem that you started with