GETDATE Value During Long Running Job

Question

GETDATE Value During Long Running Job

homebrew01

SSC Guru

Points: 55548
More actions
August 29, 2010 at 8:53 pm

#227043

I have a job that I expect to take a couple of days to run. It's a DTS package, transferring 16,000,000 records (in batches of 25,000 records), based on the following logic:
select * from MyDatabase..MyTable with (NOLOCK)
where StartDate < cast(FLOOR(cast(getdate() -367 as float)) as datetime)
I assume the value in GETDATE remains constant from the time the jobs starts, regardles of the fact that it's transferring data in batches.
Correct ?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

GETDATE wont remain constant even if it is in a loop... if you want to maintain that as constant, store it in a varibale before the loop and then use that variable inside the loop..

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Thanks for the reply. I should clarify that I'm not using a loop. It's just the 1 statement above. The 25,000 batch size is specified in the properties tab in the DTS transformation step.

Using a variable might be a good idea anyway.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Agreed. If the date mustn't change, then storing it in a variable is definitely the way to go just to be on the safe side.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)