DEV Community

Cover image for SQL-Quick tip #15 - Random dates
Allan Simonsen
Allan Simonsen

Posted on

4 3

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'
Enter fullscreen mode Exit fullscreen mode

Sql Server Management Studio screenshot

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);
Enter fullscreen mode Exit fullscreen mode

Sql Server Management Studio screenshot

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up

Top comments (0)

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More