Random value for DATETIME column

Today, I am going to describe how to generate random value for DATETIME field within a given range. This is very useful specially for generating test data. For this, we will use couple of built-in functions like:

  1. DATEDIFF
  2. DATEADD
  3. RAND
  4. ROUND

 

Random DATETIME Value

DECLARE @startDate DATETIME -- start date

DECLARE @endDate DATETIME -- end date

DECLARE @noOfSec INT -- variable

DECLARE @randomSec INT -- variable

SET @startDate = '2021-06-27 08:00 AM' -- assigning starting date

SET @endDate = '2021-06-27 08:30 AM'

-- assigning end date -- Get the number of seconds within the date range

set @noOfSec = DATEDIFF(SECOND, @startDate, @endDate)

-- Get random seconds within the date range

set @randomSec = ROUND(((@noOfSec-1) * RAND()), 0)

-- Add the random seconds to get the random datetime value within the daterange

SELECT DATEADD(SECOND, @randomSec, @startDate)

Hope this will be useful for you. Happy TSQLing!

 

Pingbacks and trackbacks (1)+

Add comment