Mehedi's Earth

In my previous article, I showed you how to generate a random datetime value for column. In this article, I will discuss you how to generate random Datetime for all rows in a query. Let's explore.

We will use couple of built-in function for this.

1. NEWID() function returns uniqueidentifier data type like: CC845D08-A2F9-4CE9-B674-BC4C49DADB1F. Unfortunately, we cannot use this. So we will use another function in combination with NEWID().
2. CHECKSUM() function computes the hash value and returns a INT value. The value could be both positive or negative.
3. ABS() function returns absolute value of its argument.
4. DATEADD() function which add a number to the datepart of an input date, and returns a updated datetime

Example

Create a TABLE and INSERT some rows.

CREATE TABLE [dbo].[Test](
    [pk_id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
    [pk_id] ASC
)
) ON [PRIMARY]

SELECT TOP (1000) [pk_id],[name], ABS(CHECKSUM(NEWID())) as RandomValue,
DATEADD(SECOND, ABS(CHECKSUM(NEWID())), GETDATE()) as RandomDate
FROM [Test]

If you wish to return a datetime within a range, then mod the random number by the values of the range + lowest value of range.

So the formula is:

Random Number % values of the range + lowest value of range

SELECT TOP (1000) [pk_id],[name], ABS(CHECKSUM(NEWID())) as RandomValue,
DATEADD(SECOND, ABS(CHECKSUM(NEWID())), GETDATE()) as RandomDate,
DATEADD(SECOND, ABS(CHECKSUM(NEWID())%20+1), '2024-05-08 17:49:04.590') as RandomDateInRange -- This will generate a random date within 20 sec of the base date
FROM [Test]

Conclusion

Expecting this will help you to generate random datetime value for each row. Happy TSQLing!