Total Pageviews

Thursday, June 12, 2014

How to get random dates in a range?

DECLARE @D1 DATE = '20000101'
DECLARE @D2 DATE = '20141231'

   ;WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cte(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
   SELECT TOP 1000
    DATEADD(DAY,ABS(CHECKSUM(NEWID())) % (1+DATEDIFF(DAY,@D1,@D2)),@D1)
   FROM  cte

No comments:

Post a Comment