Random DateTime Range

  • SQL Espo

    SSCrazy

    Points: 2483

    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.

  • Jeff Moden

    SSC Guru

    Points: 993770

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714311

  • ZZartin

    SSC-Dedicated

    Points: 30308

    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: 13298

    -- 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 2 months ago by  ben.brugman.

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

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