Search code examples
sql-servercoalescegetdate

Strange behavior of GETDATE() when used with COALESCE in SQL SERVER

DECLARE @i INT

SET @i=14

SELECT COALESCE(@i, GetDate());

Above returns

1900-01-15 00:00:00.000

now if I change i to 11, result will be 

1900-01-12 00:00:00.000

Should not GETDATE() return current datetime?

Solution

  • data type precedence

    When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.

    1900-01-01 + 14 is 1900-01-15, your int is converted to datetime since the second argument to the coalesce is datetime