Search code examples
sql-serversql-server-2017

SQL getdate() - not the same in one statement

The getDate() statement always returns the same value anywhere in one statement.

However, in one SQL Server 2017, I'm seeing otherwise.

To set this up, create a table and put two rows into it:

CREATE TABLE Test 
(
    TestDate datetime2(0) NULL,
    OtherValue varchar(5) NULL
) 

INSERT INTO Test (OtherValue) VALUES ('x')
INSERT INTO Test (OtherValue) VALUES ('x')

Then run this query a number of times:

SELECT  
    CASE 
       WHEN GETDATE() < COALESCE(TestDate, GETDATE()) 
          THEN 'less'
       WHEN GETDATE() > COALESCE(TestDate, GETDATE()) 
          THEN 'greater'
       ELSE 'same' 
    END [Compare]
FROM 
    Test

Both rows always return matching results.

When I do this in SQL Server 2008 R2 (v10.50) and other SQL Server 2017 machines, the result is always 'same'.

However, on one of my SQL Server 2017 instances, it varies randomly between 'same', 'less' and 'greater':

enter image description here

enter image description here

enter image description here

Why is this happening? Is there a server setting that can cause this?

Edit:

Using SYSDATETIME in place of GETDATE works as expected on the 'bad' server, always returning 'same'.

Edit #2:

If I test GETDATE as above on a column defined as DATETIME (which is what GETDATE() generates), then it works as expected. So it seems to be related to converting between DATETIME and DATETIME2.

Solution

  • Turns out the behaviour of getdate changed from SQL 2000 to SQL 2005.

    See https://stackoverflow.com/a/3620119/32429 explaining the old behaviour:

    In practice, GETDATE() is only evaluated once for each expression where it is used -- at execution time rather than compile time. However, Microsoft puts rand() and getdate() into a special category, called non-deterministic runtime constant functions.

    and the following discussion:

    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.