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); 
Top comments (0)