Dynamic SQL to randomize all datetime columns in a database

Question

I'm having trouble figuring out how to use a dynamic SQL Server query to change the datetime fields in my database, called OnlineStore, to be random and in the current year.

It should be noted that this relates to homework. My class is fairly unstructured, so I'm having a hard time knowing where to go from here.

My question is: How can I write a dynamic SQL Server query that may use loops or table variables and takes the 2 datetime columns in my database (Product.LastOrderDate, Orders.OrderDate) and assigns each row a random date in the current year?

This is what I have so far. I'm open to any changes.

Declare @SQL varchar(max) = '
Declare @D1 float = cast(cast(''2017-01-01 00:00:00'' as datetime) as float);
Declare @D2 float = cast(cast(''2017-12-31 23:59:59'' as datetime) as float);

Here's one half: `SELECT REPLACE(REPLACE('UPDATE $t SET $c = ...', '$t', QUOTENAME(t.[name])), '$c', QUOTENAME(c.[name])) FROM sys.tables t JOIN sys.columns c ON c.[object_id] = t.[object_id] WHERE t.is_ms_shipped = 0 AND c.system_type_id = 61`. The other half is filling in '...' with an expression that gives you a random date. Something something `RAND()`, something something `DATEDIFF`/`DATEADD`. If you need to, there's a third part with executing the resulting statements using `EXECUTE` and a cursor. It's a fun little project, for sure. — Jeroen Mostert, Aug 02 '17 at 16:37

Dave C · Answer 1 · 2017-08-03T13:33:44.423

Based on this answer I came up with the following. It randomly adds n seconds to the date 2017-01-01 capped at the number of seconds in 2017.

The first bit is just me generating a table with 50 rows, all 2017-01-01.

You'll want to focus on the parts from update on...

if object_id('tempdb..#test') is not null drop table #test
create table #test (orderDate datetime)

-- add 50 rows to test table
;with x as
(
    select 1 as t
    union all
    select t+1
    from x
)

insert into #test
select top 50 '2017-01-01'
from x
option(maxrecursion 50)

--select * from #test

update t
set OrderDate = dateadd(second,ABS(CHECKSUM(NewId())) % datediff(second,'2017-01-01 00:00:00','2017-12-31 23:59:59'),'2017-01-01 00:00:00')
from #test t

select * from #test

score 0 · Answer 2 · answered Aug 02 '17 at 16:41

Based on this answer: https://stackoverflow.com/a/18408615

This gets you almost all the way there for generating a random date, I capped the days at 28 due to February being February.

select cast('2017-' + CAST(ABS(Checksum(NEWID()) % 12) + 1 AS varchar(2)) + '-' + CAST(ABS(Checksum(NEWID()) % 28) + 1 AS varchar(2)) as date)

You can adapt this to fit your needs.

Dynamic SQL to randomize all datetime columns in a database

2 Answers2