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:

  • DATEDIFF
  • DATEADD
  • RAND
  • 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

-- 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!

This is first published here

 74,151 views last month,  127 views today

About M A A Mehedi Hasan

This is M A A Mehedi Hasan working in EBS Group since 2005. Starting career as a Software Developer, I am now Chief Technology Officer (CTO) of EBS Group. In this long journey, I have got opportunity to work with various technologies like Microsoft Data Platform, Telecom Value Added Service, and Audio/Video streaming.I have hands-on experience in managing Telecom Value Added projects, Web/App/Audio/Video streaming projects. Apart from these, I also manage large database, testing and deployment, performance tuning, long term capacity planning, and streamlining operational workflow.Being a member of techforumbd (https://www.facebook.com/techforumbd), a Bangladeshi tech community, I regularly speak and organize sessions at local and virtual PASS chapters, SQL Saturdays, and Azure conferences. In free time, I love to play with Machine Learning. By the way, you can count me as a traveler too!Stay with my personal blog: www.techearth.xyz

Leave a Reply