Use GetDate() function in While Look

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 2008 Forums

Transact-SQL (2008)

Use GetDate() function in While Look

Author

Topic

PS
Starting Member

9 Posts

Posted - 2010-02-15 : 05:42:41

I want to have GetDate to return a new datetime value every time I call the function in a loop. But the following code is returning the same datetime value every time it is called:

declare @StopTime as datetime set @StopTime = dateadd(ss,1,GETDATE())
while @StopTime > GETDATE()
begin
  select @StopTime, (select GETDATE())
end

How do I make the GetDate() function return new datetime value every time it is called? Or what other function can I use to get new datetime values in each pass of the loop?

Peter

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-15 : 05:57:53

Why do you want to do this?


declare @StopTime as datetime set @StopTime = dateadd(ss,1,GETDATE())
while @StopTime > GETDATE()
begin
  select @StopTime,  (select GETDATE())
	waitfor delay '00:00:00.100'
end

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 05:59:02

GetDate() will give you the current time on each iteration (but it is only accurate to about 3ms)

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 05:59:48

[code]

SET NOCOUNT ON

PRINT 'This will take 10 seconds to run ... be patient!!'
GO
DECLARE @intLoop int

SET @intLoop = 10

WHILE @intLoop > 1
BEGIN
SELECT @intLoop, GetDate()
WAITFOR DELAY '00:00:01'
SELECT @intLoop = @intLoop -1
END

SET NOCOUNT OFF
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-15 : 06:02:44

quote:

Originally posted by Kristen



SET NOCOUNT ON

PRINT 'This will take 10 seconds to run ... be patient!!'
GO
DECLARE	@intLoop int

SET @intLoop = 10

WHILE @intLoop > 1
BEGIN
	SELECT	@intLoop, GetDate()
	WAITFOR DELAY '00:00:01'
	SELECT	@intLoop = @intLoop -1
END

SET NOCOUNT OFF

You are too late today

Madhivanan

Failing to plan is Planning to fail

PS
Starting Member

9 Posts

Posted - 2010-02-15 : 06:27:03

Hi Kristen
Thanks for the answer.
I tred removing the select in the begin - end, and now I can see it is updating the getdate each time.
This is only one part of my query, and I want to make it to be sure that the query will stop after a set time interval.
Thanks

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 06:32:29

Sorry Madhi, I though you had just quoted back -didn't see the WAITFOR you had added .... Ho!Hum! ... more coffee needed I think!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-15 : 07:44:37

<<
more coffee needed I think!
>>

and LESS snow too

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources