locked
Getdate() called once or twice? RRS feed

  • Question

  •  

    If I have a where clause

     

    where isnull(@now,getdate()) >= getdate()

     

    Is the getdate() function called twice, so it would be possible though extremely unlikely that in the above query the getdate() could be different.

    Tuesday, June 10, 2008 4:17 PM

Answers

  • More relevant to the question:

     

    Code Snippet

    declare @d0 datetime

     

    while 1=1
    begin

      set @n = @n + 1
      if isnull(@d0, getdate()) <> getdate()
      begin
        select 'Busting out:', @n[@n]
        break
      end

    end

     

    /* -------- Sample Output: --------
                 @n
    ------------ --------------------
    Busting out: 163548

     

    (1 row(s) affected)
    */

     

    EDIT:

     

    A Question: Are you planning on actually using a column name rather than the @NOW variable?

    • Marked as answer by pituachMVP Thursday, April 18, 2019 11:25 PM
    Tuesday, June 10, 2008 4:41 PM
  • It depends on the version of SQL.

    Prior to SQL 2005, getdate() was deterministic so it was the same during the "command".

    In SQL 2005, getdate() is non-deterministic, which means every time you call it you will get a different value.

    Tuesday, June 10, 2008 9:27 PM
  • Code Snippet

    declare @n int  set @n = 0
    declare @d0 datetime
     
    while 1=1
    begin

      set @n = @n + 1
      if isnull(@d0, getdate()) <> getdate()
      begin
        select 'Busting out:', @n[@n]
        break
      end

    end
     
    /* -------- Sample Output: --------
                 @n
    ------------ -----------
    Busting out: 149634

    (1 row(s) affected)
    */

     

    select serverproperty('ProductVersion')

    /* -------- Output: --------
    -------------
    9.00.3042.00
    */

     

     

     

    Wednesday, June 11, 2008 6:35 PM

All replies

  • It is probably something you need to worry about; look at this while I run an experiment that is more on point with your question:

     

    Code Snippet

    declare @n bigint set @n = 0
    declare @d0 datetime
    declare @d1 datetime
    declare @d2 datetime

     

    while 1=1
    begin

      select
        @n = @n + 1,
        @d1 = getdate(),
        @d2 = getdate()

      if @d1 <> @d2
      begin
        select @d1[@d1],@d2[@d2],@n[@n]
        break
      end

    end

     

    /* -------- Sample Output: --------
    @d1                     @d2                     @n
    ----------------------- ----------------------- --------------------
    2008-06-10 12:41:06.843 2008-06-10 12:41:06.857 108070
    */

     


     

    Tuesday, June 10, 2008 4:37 PM
  • More relevant to the question:

     

    Code Snippet

    declare @d0 datetime

     

    while 1=1
    begin

      set @n = @n + 1
      if isnull(@d0, getdate()) <> getdate()
      begin
        select 'Busting out:', @n[@n]
        break
      end

    end

     

    /* -------- Sample Output: --------
                 @n
    ------------ --------------------
    Busting out: 163548

     

    (1 row(s) affected)
    */

     

    EDIT:

     

    A Question: Are you planning on actually using a column name rather than the @NOW variable?

    • Marked as answer by pituachMVP Thursday, April 18, 2019 11:25 PM
    Tuesday, June 10, 2008 4:41 PM
  • I did a test too.  It seems to be the same if it is in the same batch.

     

     

     

    select getdate()
    UNION ALL
    Select  getdate()
    UNION ALL
    Select  getdate()
    UNION ALL
    Select  getdate()
    UNION ALL
    Select  getdate()

     

    go
    Select  getdate()

    Tuesday, June 10, 2008 4:47 PM
  •  Ringus123 wrote:

     

    If I have a where clause

     

    where isnull(@now,getdate()) >= getdate()

     

    Is the getdate() function called twice, so it would be possible though extremely unlikely that in the above query the getdate() could be different.

     

    Can not give any advice without knowing what are you storing in the variable @now and how it should compare to current time. You could see it in the execution plan, if the scalar expression is being calculated more than once.

     

    Anyway, I know of a person who had problems with getdate() being executed more than once. Check this blog.

     

    When a Function is indeed a Constant

    http://sqlblog.com/blogs/andrew_kelly/archive/2008/03/01/when-a-function-is-indeed-a-constant.aspx

     

     

    AMB

    • Proposed as answer by Naomi N Monday, August 27, 2012 3:22 AM
    Tuesday, June 10, 2008 5:43 PM
  • I'm not sure of your situation.  You might use this as a work around:

     

    Code Snippet
    where IsNull(@Now, '01 Jan 2050') >= GetDate()

     

     

     

    Tuesday, June 10, 2008 6:03 PM
  • In order to be a valid test, the durtion of the query needs to be longer than 3/100 seconds.  Unless you're running SQL Server on DX2 66 (or some such other relic), that query will probably execute so quickly

     

     

    Here is a different test:

     

    Code Snippet

    create table #TEmp (RowGUID uniqueidentifier not null default NewID()

    , InsertDate datetime

    , SubQ tinyint)

     

    insert into #TEmp(InsertDate, SubQ)

    select GetDate(), 1

    from sys.objects as S1, Sys.Objects as s2

    union all

    select getdate(), 2

    from sys.objects as S3, Sys.Objects as s4

    union all

    select getdate(), 3

    from sys.objects as S5, Sys.Objects as s6

    union all

    select getdate(), 4

    from sys.objects as S7, Sys.Objects as s8

     

     

    insert into #TEmp(InsertDate, SubQ)

    select getdate(), 5

    from sys.objects as S9, Sys.Objects as s10

     

    select min(InsertDate), Max(InsertDate) from #Temp

    select min(InsertDate), Max(InsertDate) from #Temp where SubQ <> 5

     

    drop table #TEmp

     

     

    Tuesday, June 10, 2008 6:24 PM
  • It depends on the version of SQL.

    Prior to SQL 2005, getdate() was deterministic so it was the same during the "command".

    In SQL 2005, getdate() is non-deterministic, which means every time you call it you will get a different value.

    Tuesday, June 10, 2008 9:27 PM
  • Acutally, in your example, it is way worse.

    You have no control over the order the getdate() is called.  It is possible the getdate() on the right will be called first and will never be >= the left side.

    You need to rewrite this query.

    Tuesday, June 10, 2008 9:28 PM
  •  Tom Phillips wrote:

    Prior to SQL 2005, getdate was deterministic so it was the same during the "batch".

     

    Is that right? Was it not per statement rather than per batch?

     

    If you run:

     

    Code Snippet

    SELECT GETDATE()

    WAITFOR DELAY '00:00:01'

    SELECT GETDATE()

    GO

     

     

    ...I'd be surprised if the same value was returned for the two SELECT statements.

     

    Chris

    Tuesday, June 10, 2008 9:32 PM
  • Chris,

    Sorry, I said "batch" I should have said "command" (I edited my response above).  Yes, you will get 2 different values in your example.

    In SQL 2000 if you did something like this

    INSERT INTO tbl (fields, LOADDATE)
    SELECT fields, GETDATE() FROM tblb

    you would get the same date/time for all records inserted.

    This same command In SQL 2005, reruns GETDATE() for every single record selected from tblb and gives you potentially unique values for each record.  Also causes HUGE performance problems if you are inserting say, 17 million rows at a time.

    This has caused me many a headache, as we use this code to do batch date/times in many tables.  This was a very simple way to back out a "batch" of transactions, because everything had the same date/time.  Now in 2005, that is not true.

    Now you ask yourself,  is the value of LOADDATE the value at the SELECT time or the INSERT time.  It is the SELECT time.  Smile

    FYI, the solution to the problem in 2005 is to create the LOADDATE first

    DECLARE @loaddate DATETIME
    SET @loaddate = GETDATE()

    INSERT INTO tbl (fields, LOADDATE)
    SELECT fields, @loaddate FROM tblb

    Now every record has the same value.

    Wednesday, June 11, 2008 2:03 PM
  •  Ringus123 wrote:

     

    If I have a where clause

     

    where isnull(@now,getdate()) >= getdate()

     

    Is the getdate() function called twice, so it would be possible though extremely unlikely that in the above query the getdate() could be different.

     

    No, the getdate() value won't change. Even if the query takes hours to execute.

     

    And how do you confirm it?

    Simple. Just show the getdate() column in output.

    So, if you write a query like 

     

    Select GetDate() As CurrentTime, MyDate From VeryHugeTable

    Where Bla Bla Bla...

     

    No matter for how much time the query runs, CurrentTime column would always have just one value.

     

    However, in procedure prefer using a datetime variable and then use this variable in where clause.

     


    Regards,

    Sandeep.

    Wednesday, June 11, 2008 2:30 PM
  • Weird; is there something wrong with the experiments that I ran?  It appears that both experiments indicate that the values can be different.  Help please?

     

    Wednesday, June 11, 2008 2:57 PM
  • An intriguing thread is this!

     

    When I run the following queries on my laptop I get the same result for each GROUP BY query - only one distinct value of CreatedOn in each case.

     

    Code Snippet

    CREATE TABLE #TmpWithDefault (ID INT, CreatedOn DATETIME DEFAULT(GETDATE()))
    GO
    INSERT INTO #TmpWithDefault(ID)
    SELECT so1.object_id
    FROM master.sys.objects so1
     CROSS JOIN master.sys.objects so2
     CROSS JOIN master.sys.columns sc
    GO
    SELECT CreatedOn, COUNT(*)
    FROM #TmpWithDefault
    GROUP BY CreatedOn
    GO

    --2744000 Rows. 21s Duration, 1 Distinct Value of CreatedOn


    CREATE TABLE #TmpNoDefault (ID INT, CreatedOn DATETIME)
    GO
    INSERT INTO #TmpNoDefault(ID, CreatedOn)
    SELECT so1.object_id, GETDATE()
    FROM master.sys.objects so1
     CROSS JOIN master.sys.objects so2
     CROSS JOIN master.sys.columns sc
    GO
    SELECT CreatedOn, COUNT(*)
    FROM #TmpNoDefault
    GROUP BY CreatedOn
    GO

    --2744000 Rows. 24s Duration, 1 Distinct Value of CreatedOn


    SELECT @@VERSION
    --Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb  9 2007 22:47:07   Copyright (c) 1988-2005 Microsoft Corporation  Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
    G0

     

     

     

    Tom - what edition and SP of SQL Server 2005 did you find this with?

    Kent - which edition and SP did you perform your experiments on?

     

    Chris
    Wednesday, June 11, 2008 6:13 PM
  • Code Snippet

    declare @n int  set @n = 0
    declare @d0 datetime
     
    while 1=1
    begin

      set @n = @n + 1
      if isnull(@d0, getdate()) <> getdate()
      begin
        select 'Busting out:', @n[@n]
        break
      end

    end
     
    /* -------- Sample Output: --------
                 @n
    ------------ -----------
    Busting out: 149634

    (1 row(s) affected)
    */

     

    select serverproperty('ProductVersion')

    /* -------- Output: --------
    -------------
    9.00.3042.00
    */

     

     

     

    Wednesday, June 11, 2008 6:35 PM
  • It looks to me like this result is consistent with what Tom was saying; it looks like it is dependent on exactly what version you are running?

     

    Wednesday, June 11, 2008 6:54 PM
  •  Kent Waldrop Jn08 wrote:
    It looks to me like this result is consistent with what Tom was saying; it looks like it is dependent on exactly what version you are running?

     

     

    Hmm, I see the same as you except my machine only manages around 15000 iterations.

     

    This isn't quite what Tom was getting at - he specifically referred to differences during inserts, which I can't repeat. Out of interest if you run the code that I previously posted then do you get a similar result, i.e. only one distinct datetime?

     

    Chris

    Wednesday, June 11, 2008 7:19 PM
  • I get the same results as you did -- again, just with a different time period.

    Wednesday, June 11, 2008 7:59 PM
  • Wow what a great response, many thanks for that so there does appear to be problems with 2005, but  SQL 2000 is not effected? Many Thanks

    Thursday, June 12, 2008 8:06 AM
  • Please see this blog.  It discusses the same exact problem:

    http://sqlblog.com/blogs/andrew_kelly/archive/2008/03/01/when-a-function-is-indeed-a-constant.aspx

    This was a change in SQL 2005, in prior version of SQL results would always have the same date/time.

    The reason yours works for 15000 records is probably the speed of the inserts on your machine and the resolution of the clock.

    Thursday, June 12, 2008 8:34 PM
  • It is possible the problem I described has been fixed in some release of 2005 and I just don't know it.  I have not looked at this problem in some time, since changing from 2000 to 2005, but we had to recode all our code in order to bypass the functional difference with getdate() in SQL 2005.  I am unable to duplicate my issue in 9.0.2221 or 9.0.2153, but it did happen.

    With that said, GETDATE() is still non-deterministic and is not guaranteed to be the same value for each call.  You should not use getdate() in this way. Even if it works now, it may not work in the future, again.  Smile

    Thursday, June 12, 2008 9:27 PM
  • WHILE DATEDIFF(ms, GETDATE() , GETDATE()) = 0 

    PRINT 'This will not run in an infinite loop'


    Does not run in an infinite loop in any version of SQL Server that I have tested. 2000/2005/2008.

    GETDATE() has never been deterministic. "Deterministic functions always return the same result any time they are called with a specific set of input values.". Clearly this is not the case because of the nature of the function.

    See the list of deterministic functions in SQL Server 2000

    GETDATE is a Runtime Constant Function. A single GETDATE() invocation will not return a different result per row regardless of length of query execution though you can wrap in a UDF to get this effect. 

    Sunday, August 26, 2012 2:23 PM