Curious Time/Date question....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Curious Time/Date question....

Author

Topic

interclubs
Yak Posting Veteran

63 Posts

Posted - 2002-08-22 : 18:50:19

I need to drop a time/date stamp into my database for about 200,000 records. Anyone have any ideas how to write a stored proc that will randomly pick a date within the last year and insert it? Thanks!

xega
Starting Member

10 Posts

Posted - 2002-08-22 : 20:15:02

Clubs de Inter,

Try this:

Lets say your table name is test and the date field name is test_date.

---------------------------------------------------------------------


DECLARE UpdateRndDates CURSOR FOR
SELECT * FROM test

OPEN UpdateRndDates
FETCH NEXT FROM UpdateRndDates

WHILE @@FETCH_STATUS = 0
BEGIN
   UPDATE test 
   SET test_date = DATEADD(D, CAST(RAND() * 364 AS INT), 
   '01-Jan-2001')
   WHERE CURRENT OF UpdateRndDates
   FETCH NEXT FROM UpdateRndDates
END

---------------------------------------------------------------------

Regards
Xega :)

Whenever I start to think, my brain freezes therefore I think I'm cool...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-22 : 20:26:22

Why use a cursor?

DECLARE @date datetime
UPDATE test
SET test_date = DATEADD(DD, RAND(convert(varbinary, newid()))*364, '01-Jan-2001')

<edit> to generate random date sequence </edit>

Edited by - robvolk on 08/22/2002 20:34:42

xega
Starting Member

10 Posts

Posted - 2002-08-22 : 20:45:36

Wow!

You edit your replies very fast

. I was about to say you your
first reply would not work.

Nevertheless Great job.

Now, try solving my problem dude

:

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19066[/url]

Regards
Xega

Whenever I start to think, my brain freezes therefore I think I'm cool...

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-23 : 09:50:41

Rob's stealing my line.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources