CODEWITHSUNDEEP
sqlsql-server
l217
l217

Reputation: 93

T-SQL: how to update time column with random times

I am using SQL Server 2012 and T-SQL. I have a time column of 200 rows with different times (hh:mm:ss). I need to update it with random times. So all the data times I have now will be updated with new data times. Thank you!

Upvotes: 1

Views: 895

Answers (3)

softdevlife
softdevlife

Reputation: 98

Assume you have the following 3 rows with a time column that contains different times:

CREATE TABLE table_with_times (timecolumn TIME);

INSERT INTO table_with_times VALUES ('20:00:00'),('05:00:59'),('22:30:50');

You can update each time column to new random time by computing random values for hour/minutes/seconds:

select * from table_with_times;

UPDATE table_with_times SET timecolumn = CAST(RIGHT('00' + CAST(ABS(CHECKSUM(NewId())) % 24 AS VARCHAR(2)),2)+':'+ RIGHT('00' + CAST(ABS(CHECKSUM(NewId())) % 60 AS VARCHAR(2)),2)+':'+ RIGHT('00' + CAST(ABS(CHECKSUM(NewId())) % 60 AS VARCHAR(2)),2) AS TIME);

select * from table_with_times;

Try it on http://sqlfiddle.com/

Upvotes: 0

Gabriel Menezes
Gabriel Menezes

Reputation: 119

UPDATE <YOUR_TABLE> SET <TIME_COLUMN> = DATEADD(second, Rand(Cast(Newid() AS VARBINARY)) * datediff(second,'00:00:00', '23:59:59'), '00:00:00')

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270351

Here is one method:

select cast(dateadd(second, rand(checksum(newid()))*60*60*24, 0) as time)

Upvotes: 3

Related Questions