3

I understand from sources like this that GETDATE() should always (well, eventually, depending on how fast the loop is) return a different value in a loop.

I have this TSQL:

DECLARE @WaitUntilTime DATETIME = DATEADD(SECOND, 10, GETDATE())

WHILE (DATEDIFF(SECOND, GETDATE(), @WaitUntilTime) > 0)
BEGIN
    SELECT GETDATE(), @WaitUntilTime
END

But it's always outputting the same value for GETDATE(), and the loop never ends after 10 seconds like I want it to. Why?

I also found this answer which sounds like a similar case, but it talks about GETDATE() being evaluated once for a column in a SELECT query. I don't know if that applies to a while-loop as well.

By the way, I know about WAITFOR but I can't use it because I want to introduce a delay within a single batch.

Community
  • 1
  • 1
rory.ap
  • 34,009
  • 10
  • 83
  • 174

1 Answers1

2
DECLARE @WaitUntilTime DATETIME = DATEADD(SECOND, 10, GETDATE())
DECLARE @Dummy int

WHILE (DATEDIFF(SECOND, GetDate(), @WaitUntilTime ) > 0)
BEGIN
    Set @Dummy=1
END
SELECT GetDate(), @WaitUntilTime

Returns after a 10 second delay

(No column name)        (No column name)
2016-10-18 13:53:20.000 2016-10-18 13:53:20.140
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • Nice, thanks! Can you explain what is wrong with my way? The question is really about *why* this is happening. – rory.ap Oct 18 '16 at 17:56
  • @rory.ap You stacked-up 10,072,069 SELECTS .. changed dummy to a counter (just for fun) – John Cappelletti Oct 18 '16 at 18:03
  • @JohnCappelletti -- Still having trouble understanding -- is it because selects take a long time compared to var assignment, and it would eventually get to the end (after it ran through the 10M selects)? Note, I had first tried something similar to your approach before I posted this question, but I had `SELECT 1` inside the loop. I was looking for a no-op. If my hypothesis is true (due to select overhead), the same explanation would apply, and I just shot myself in the foot by doing that. – rory.ap Oct 18 '16 at 18:10
  • @rory.ap The Select overhead was surprising. I put your original select in place of the loop and and set the duration to 1 second. It generated 176 selects in 12.362 seconds. – John Cappelletti Oct 18 '16 at 18:15
  • Well, thanks for the help, from one New Englander to another! – rory.ap Oct 18 '16 at 18:19
  • 1
    @rory.ap I keep telling my wife I belong in St. John's in the Virgin Islands. – John Cappelletti Oct 18 '16 at 18:26