Random DateTime Range

Question

Post reply

Random DateTime Range

SQL Espo

SSCrazy

Points: 2678
More actions
April 15, 2019 at 6:20 pm

#3384314

I understand my topic title is pretty generic, but what I cant seem to find on the internet is how to generate a small table of random date and time values for the year 2019. An example is
2019-01-01 14:01:00.0000000
2019-01-02 14:01:00.0000000
2019-03-01 06:13:48.0000000
2019-04-01 05:47:40.0000000
2019-04-13 04:06:51.0000000
2019-04-15 03:23:28.0000000
I am sure it is more simple than I think...but I am really beating my head against my desk and any help is appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

Please see the following article...

https://www.sqlservercentral.com/articles/generating-test-data-part-2-generating-sequential-and-random-dates

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 2

This article might help: https://www.sqlservercentral.com/articles/generating-test-data-part-2-generating-sequential-and-random-dates

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 3

You could try something like this,

CREATE TABLE #TESTY(COL_ONE datetime) 

DECLARE @seconds int = 31536000 --total seconds in the year

INSERT INTO #TESTY
SELECT DATEADD(second, CAST(FLOOR(RAND(CAST(NEWID() AS varbinary )) * @seconds) AS int) , '20190101')
FROM INFORMATION_SCHEMA.COLUMNS

ben.brugman SSChampion Points: 13369 More actions · Answer 4

-- ben brugman
-- 20190416
-- Generate random date's within 2019

declare @start datetime = '20190101'
;
WITH
  L0 AS(SELECT 0 AS c UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),   -- 4
  L1 AS(select 0 as x from L0 A, L0 B, L0 C, L0 D),                                -- 4 ^4 = 256
  L2 AS(select 0 as x from L1 A, L1 B, L1 C, L1 D),                                -- (4 ^ 4) ^4 =  4 Giga
  L9 AS(Select row_number() OVER(PARTITION BY 1 order by x ) as P from L2),        -- add numbers
  R  AS(SELECT p
               , @START+convert(integer,(ROUND(rand(checksum(newid()))* 365,0,1))) new_date1
               , @START+convert(datetime,     (rand(checksum(newid()))* 365     )) new_date2
        FROM L9)
SELECT * FROM R WHERE p < 10000

Generating random numbers comes with some 'difficulties'. Newid is introduced to generate a different number for each row. When generating be carefull with the 'borders', can they be generated or not. Be carefull with the first of januar of the next year.

Ben

Or a short method:

SELECT '20190101'+convert(datetime,(rand(checksum(newid()))* 365)) ok into DT FROM sys.objects

This reply was modified 5 years, 7 months ago by ben.brugman.