Selecting GETDATE() function twice in a select list-- same value for both?

Question

I have a SELECT statement that uses GETDATE() for two different column values. I'm wondering if by the nature of things those two separate function calls in the same SELECT will return identical values every time?

Iconoduly

Answer 1

No they aren't guaranteed to return identical values every time. Each individual reference to GetDate() is a runtime constant and will keep its value throughout the query...

SELECT GETDATE()
FROM large_table

will return the same value in all rows regardless of how long the query takes to run.

But there is no guarantee that different references will have the same value.

You can see this as below

SET NOCOUNT ON;

DECLARE @T TABLE 
(
rownum INT IDENTITY(1,1) PRIMARY KEY,
d1 DATETIME,
d2 DATETIME
)

WHILE (5 > (SELECT COUNT(*) FROM @T WHERE d1 <> d2))
    BEGIN
    DELETE FROM @T WHERE d1 = d2
    INSERT INTO @T 
    SELECT GETDATE(),GETDATE()
    END

SELECT * FROM @T

Example Results

rownum      d1                      d2
----------- ----------------------- -----------------------
22381       2011-05-18 12:24:14.433 2011-05-18 12:24:14.437
30912       2011-05-18 12:24:15.420 2011-05-18 12:24:15.423
43234       2011-05-18 12:24:16.717 2011-05-18 12:24:16.720
113360      2011-05-18 12:24:24.210 2011-05-18 12:24:24.213
147855      2011-05-18 12:24:27.817 2011-05-18 12:24:27.820

Imprudent

Answer 2

Sorry to say it, but I just came up with a test to show that it will not always return the same value. It actually does get evaluated twice, and if the system clock happens to turn over during the time between those two evaluations, you could get slightly different times out of the two calls.

However, the others are right in saying that it won't be evaluated once per row: only once per column.

See Will GETUTCDATE() return the same value if used twice in the same statement?

Avalon

Answer 3

Most system functions are evaluated per query, not per row, except those that are by definition statistically unique such as NEWID(). This includes things like RAND() (which is not unique, only psuedo random) unless seeded randomly row by row with NEWID().

Determinism is not related to this evaluation because it means "the output is the same for any given input independent of, say, language or DMY/MDY)

This "once per query" evaluation makes sense of course, especially for GETDATE().

If I do a 10k row update, I want every row to have the same GETDATE() value. The entire update could easily take > 3.33 milliseconds and I don't want different values over my 10k rows.

Toni

This would be a once-per-function-per-query, so if the query has multiple instances of GETDATE() in it, each of those would cause an invocation of the GETDATE() function. – Avalon

@Avalon sqlfiddle.com/#!6/27bad6/1 says otherwise, and GETDATE() is run once per query – Toni

Well, I'm not entirely clear on the inner workings of the execution plan. I can totally see why you'd expect that example to surface GETDATE() getting executed twice. However, I'm guessing that it's not doing what you expect. sqlfiddle.com/#!6/27bad6/7/0 shows that even without the delay call, GETDATE() will end up with different values every so often. – Avalon

Answer 4

Yes, they will return the same date and time, to the millisecond.

SELECT GETDATE(), GETDATE()

Returns

2010-10-29 15:34:06.353 2010-10-29 15:34:06.353

I have tested this with 4000, GETDATE() and they all return the same.

Outwit

This is not reliable behavior. See my post. – Avalon

Yep. 4,000 is not high enough to reliably see this behaviour as my answer shows. – Imprudent

-1 since this is unfortunately not correct, see answer by @MartinSmith – Duchess