SQL-Quick tip #15 - Random dates

Sql Server tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Random date

This is a simple but useful tip i have used many times working on different projects, where i needed some testdata that should contain random dates.
The script below show how you can generate random dates. And you can combine this tip with SQL-Quick tip #5 - Create a sequence of date and time

SELECT DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 365), CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR(4)) + '-01-01') AS [Random date], 'Random date in the current year' AS Comment
UNION
SELECT DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 365), '2000-01-01'), 'Random date in the specific year'
UNION
SELECT DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2000-01-01'), 'Random date between 2000-01-01 and 2010-01-01'
UNION
SELECT DATEADD(DAY, (ABS(CHECKSUM(NEWID())) % 65530), 0), 'Random date between 1900-01-01 and 2079-06-06'

The script below will generate a sequence of dates and times with an interval of 30 minutes for 2 days from the random starting date

DECLARE @stepSizeInMinutes INT = 30; -- Change this line to change the time interval
DECLARE @from DATETIME2 = DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 365), CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR(4)) + '-01-01');
DECLARE @to DATETIME2 = DATEADD(DAY, 2, @from);
-- Create Recursive Discrete Table
WITH Recursive_CTE AS
(
       SELECT @from AS TimestampUtc
        UNION ALL
       SELECT DATEADD(MINUTE, @stepSizeInMinutes, TimestampUtc) 
         FROM Recursive_CTE
        WHERE TimestampUtc < @to
)
SELECT *
  FROM Recursive_CTE
 ORDER BY TimestampUtc
OPTION (MAXRECURSION 0);