Does SQL Server have any consistency for the result of GETDATE() as time passes?

Question

It seems obvious that if you call GETDATE() in SQL Server in two execution batches sufficiently far enough apart in time, the return values will be different.

On the other hand, if you call GETDATE() twice in rapid succession, you are likely to get the same result from both calls because of the finite resolution of the result. But that is not necessarily assured, as the two calls could occur in real time on either side of a multiple of the resolution of the result.

My question is: Is there any scope (in a vague sense) that guarantees that all GETDATE() calls will return the same value? If you call GETDATE() multiple times in the same expression? For a single row (but different expressions) within a DML statement? For the entire duration of a single statement? Including any triggers that execute? What if a user procedure is called?

The question comes up when making an expression to yield, for example, the date of first day of the month.

DATEADD(day, 1-DATEPART(day, GETDATE()), GETDATE()) would appear to be the way to do this, but that only works if you are 100% certain that both GETDATE calls return the same value, even if you run the code right across the boundary between the last day of one month and the first of the next. If one of the GETDATE calls could return 00:00:00.000 time of the first of the new month but the other one return one unit of resolution before that you would get undesired results (the second of the month that just ended, or the last day of the month that just ended, depending on which GETDATE call returned the smaller result.

You could dodge the problem to some extent by setting a variable to the result of GETDATE before executing the DML statement, and referring to that variable rather than calling GETDATE within the DML statement, but this is only available in the lexical context; procedures you call and triggers you invoke will still get their own GETDATE values.

The same question applies to all the other date-getter functions like SYSDATETIME etc, both among multiple calls to each but also among calls to several of these functions.

Answer 1

Almost all built-in SQL Server functions are evaluated once per statement, including GETDATE() and RAND(). The notable exception in NEWID() which runs once per row in a query.

eg this

use tempdb
go

create or alter function fn_spin(@iters int)
returns int
as
begin
  declare @hash varbinary(2000) = 0x01
  while @iters > 0
  begin
     set @hash = HASHBYTES('sha-512',@hash) 
     set @iters -= 1
  end
  return @iters;
end
go
select getdate();
select top 10 dbo.fn_spin(1000000), getdate()
from sys.objects 
union all
select 1, getdate();
select getdate();

Outputs

-----------------------
2021-09-20 17:22:07.040

(1 row affected)

            
----------- -----------------------
0           2021-09-20 17:22:07.040
0           2021-09-20 17:22:07.040
0           2021-09-20 17:22:07.040
0           2021-09-20 17:22:07.040
0           2021-09-20 17:22:07.040
0           2021-09-20 17:22:07.040
0           2021-09-20 17:22:07.040
0           2021-09-20 17:22:07.040
0           2021-09-20 17:22:07.040
0           2021-09-20 17:22:07.040
1           2021-09-20 17:22:07.040

(11 rows affected)


-----------------------
2021-09-20 17:22:11.147

(1 row affected)

But I don't think this behavior is documented anywhere, so while it's unlikely to change, you probably shouldn't rely on it, and assign a variable to the return value of GETDATE() and use that in a subsequent query.

Answer 2

GETDATE() is a "runtime constant". It behaves the same as RAND() each individual reference to it in the query will retain the same value regardless of how long the query takes to execute - however if there are multiple calls to it in the query text they can return different results

1. If you call GETDATE() multiple times in the same expression? Can be different. This isn't an infinite loop WHILE DATEDIFF(MILLISECOND, GETDATE(), GETDATE()) = 0 Loop:
2. For a single row (but different expressions) within a DML statement? Can be different
3. For the entire duration of a single statement? Yes. This is the runtime constant behaviour
4. Including any triggers that execute? No. If the triggers call GETDATE() this is a different invocation so may be different
5. What if a user procedure is called? Same as triggers

You could potentially use SESSION_CONTEXT to save the value in a parent scope and have it available in child scopes (most useful for triggers as you can't pass params to them).

Answer 3

Based on answers so far, it would appear that each call is evaluated once for the duration of the statement that lexically contains it, and so each call will have a consistent value through the execution of the statement, but that multiple calls to the same function within that same statement or expression could actually return separate values.

Thus my first-of-the-month expression DATEADD(day, 1-DATEPART(day, GETDATE()), GETDATE()) could indeed return a result that is not the first of the current month.

Based on answers so far, this is unfortunately also undocumented behaviour.

I've thought of other workarounds to ensure I reliably get the first of the month.

One way is to do the calculation in a user-defined function, to which the result of the (single) call to GETDATE() is passed. Based on looking at the execution plans, making this function schemabound might improve performance a bit.

Another way is to use a subquery to return the current date:

select DATEADD(day, 1-DATEPART(day, now), now)
from (values(GETDATE()) as x(now)

though as the example shows, this requires embedding the DATEADD expression in a query so you have a FROM clause to work with. In a non-query context this would mean wrapping the SELECT above in parentheses to make a scalar subquery of it.

Something similar could be done using a CTE ("WITH" clause).

As for performance, I used a CTE to make 2^18 evaluations of these expressions, and the only one that took noticeably more time was the call to the non-schemabound function. Using the schemabound function might be a tad faster than using the (VALUES(GETDATE())) table value but the difference is getting close to measurement noise.

I think an exception would have to be a function call in a grouping expression, and occurrences of the same expression in the select-list. If this expression gets re-evaluated to produce the result of the select-list you could get two groups that appear to have the same grouping value. As an example of this,

select b+cast(substring(cast(newid() as varbinary(16)), 1, 8) as bigint),
    b+cast(substring(cast(newid() as varbinary(16)), 1, 8) as bigint),
    cast(substring(cast(newid() as varbinary(16)), 1, 8) as bigint)
    from (values(0), (0), (1), (3)) as a(b)
group by b+cast(substring(cast(newid() as varbinary(16)), 1, 8) as bigint)

yields a sample result of:

-------------------- -------------------- --------------------
-5107509648495737269 -5107509648495737269 8195184844907205184
-452950139019548596  -452950139019548596  1904159118162822984
5398350913277430862  5398350913277430862  7322783292314643527
8375842013571548745  8375842013571548745  -3125311700796538546

(4 row(s) affected)

Note how the two first columns both use exactly the same newid() result, which is the one used for grouping. The third column does not reference any of the columns in the from-clause and so it not recognized and gets its own separate result from the newid() call. I admit this is not a perfect example because newid is special insofar as it is re-evaluated for each output row, while getdate is (apparently) not.