I am using in a Bash Shell Script this code for retrieving a random date.
Is there a way to do that also in Microsoft SQL Server?
I can imagine that through a single SELECT query and probably by calling a few functions that can be performed easily, somehow.
Well, all you really need to do is decide on your date range, then pick a random number between 0 and the delta in days (+1), then add that many days to the start of the range.
DECLARE @start DATE = '19700101', @end DATE = SYSDATETIME();
SELECT DATEADD
(
DAY,
(ABS(CHECKSUM(NEWID())) % (DATEDIFF(DAY, @start, @end)+1)),
@start
);
There are other methods you can use to achieve randomness, including RAND() and CRYPT_GEN_RANDOM(). In my experience, distribution is not particularly fantastic in any of them, and some will behave differently in a query than others, so they may or may not be adequate depending on your needs. I followed up on a comment from Paul White, but saw similarly poor distribution with the following query (of course quality of distribution is also a factor of the size of the range):
DECLARE @start DATE = '19700101', @end DATE = SYSDATETIME(), @r INT;
SET @r = RAND(CHECKSUM(NEWID())) * (DATEDIFF(DAY, @start, @end)+1);
SELECT DATEADD(DAY, @r, @start);
Documentation:
The only true randomizing I have found within SQL Server is to take a table and order it by newid(). So in this case, create a table with the dates you want to be valid. Then
Select top 1 dt
from dt_table
order by newid()
