9

I need to get a random date-time within a range of 2 date-times in T-SQL for MS-SQL server 2012. It is necessary for me that it goes to the precision of seconds, I need DATETIME2(0) as datatype for both input and output. I have done some research online but the answers I could find are either for dates only or are for other database types.

The code should do something like:

Input: ('2015-01-01 08:22:13' , '2015-03-05 17:56:31') <-- I will use a select statement to replace these absolute numbers

Return: ('2015-02-11 14:32:45') <--this should be randomized

Can anyone help me out with this?

Thanks in advance

sjoerd haerkens
  • 95
  • 1
  • 1
  • 5

1 Answers1

20

One option is to randomize the numbers of seconds between FromDate and ToDate, and then add it to FromDate. Hope it works for you.

DECLARE @FromDate DATETIME2(0)
DECLARE @ToDate   DATETIME2(0)

SET @FromDate = '2015-01-01 08:22:13' 
SET @ToDate = '2015-03-05 17:56:31'

DECLARE @Seconds INT = DATEDIFF(SECOND, @FromDate, @ToDate)
DECLARE @Random INT = ROUND(((@Seconds-1) * RAND()), 0)

SELECT DATEADD(SECOND, @Random, @FromDate)
pcofre
  • 3,976
  • 18
  • 27