Because Ramesh's original question was why doesn't
give 2 as the answer. Darek tried to explain that the COALESCE is sensitive to data type precedence. Before the coalesce function
is run, it checks what data types it is comparing. i.e. INT, INT, INT and DATETIME. It decides that for this function they should all be
processed as DATETIME and implicitly converts them before it calls the funtion. So 2 becomes 1900-01-03 00:00:00.000 (as
SELECTCAST(2 AS DATETIME)
10 becomes 1900-01-11 00:00:00.000 and 5 becomes 1900-01-06 00:00:00.000.
Note that 0 always equals 1900-01-01 00:00:00.000 when representing datetime values as integers.
Coalesce then selects the first non-null value and returns 1900-01-03 00:00:00.000.
There is an inherent danger in using mixed data types in the same function but had the original question
constructed the SELECT statement in either of the following three ways, the answer of 2 would have been returned.
SELECT CAST(COALESCE(null,2,10,5,getdate()) AS INT)
Finally, try playing around with the values set in the following code and see the different results
DECLARE @i1 INT,
SET @i1 = NULL
SET @i1 =3
SET @i3 = NULL
SET @i4 = NULL
SET @d5 =GETDATE()
IF COALESCE(@i1, @i2, @i3, @i4, @d5) < ISNULL(@d5, '2099-12-31')
SELECT CAST( COALESCE(@i1, @i2, @i3, @i4, @d5) AS INT)
SELECT COALESCE(@i1, @i2, @i3, @i4, @d5)