Post reply

getdate() in an update query vs. datetime variable

  • July 9, 2009 at 7:37 am

    #131881

    Hi folks,

    Periodically I get a request from the business folks to update a date column to today's date for a whole bunch of articles. After loading those articles into a temp table, I generate a query along the lines of:

    update target_table

    set DateChanged = getdate()

    where articleNumber in (select articleNumber from #temp_Articles)

    Is getdate() guaranteed to be the same for all the changes, as this is occurring in one batch or would it be better to assign getdate() to a variable first then replace the update with set DateChanged = @today?

    Thanks.

    Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

  • July 9, 2009 at 8:43 am

    #1022426

    I *think* it's called for each row

  • July 9, 2009 at 8:48 am

    #1022434

    getdate() will return a consistant value within a statement. it doesent matter how long it take for the statement to execute.

    edit : See here for more info http://sqlblog.com/blogs/andrew_kelly/archive/2008/03/01/when-a-function-is-indeed-a-constant.aspx


    Clear Sky SQL
    My Blog[/url]

  • July 9, 2009 at 1:40 pm

    #1022641

    Dave Ballantyne (7/9/2009)

    getdate() will return a consistant value within a statement. it doesent matter how long it take for the statement to execute.

    edit : See here for more info http://sqlblog.com/blogs/andrew_kelly/archive/2008/03/01/when-a-function-is-indeed-a-constant.aspx

    Hmmm...I'll simulate this on a large fragmented table, no indexes, to insure enough lag. Seems the blog also had a difference of opinion, unresolved. For now, to play it safe, I'll use a variable, just to be sure, until I can verify this. Will let everyone know once done.

    Thanks. 🙂

    Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

  • July 9, 2009 at 1:52 pm

    #1022646

    Gaby Abed (7/9/2009)

    Dave Ballantyne (7/9/2009)

    getdate() will return a consistant value within a statement. it doesent matter how long it take for the statement to execute.

    edit : See here for more info http://sqlblog.com/blogs/andrew_kelly/archive/2008/03/01/when-a-function-is-indeed-a-constant.aspx

    Hmmm...I'll simulate this on a large fragmented table, no indexes, to insure enough lag. Seems the blog also had a difference of opinion, unresolved. For now, to play it safe, I'll use a variable, just to be sure, until I can verify this. Will let everyone know once done.

    Thanks. 🙂

    Okay, definitely confirmed that getdate() in a statement is the same, regardless of how long the query takes. I created a very basic table of a dummy data column and a datetime default getdate() column. In inserted into the table from itself 21 times (doubled each loop) to get over 1,000,000 rows, and did the update. A couple seconds passed that should have registered the difference.

    Thanks all.

    Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

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

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