Today I was presented with an issue that looked initially like a bug in the COALESCE function, but turned
out to be part of the function's documented behaviour. I've reduced the code right down to the minimum
necessary to demonstrate the issue :
DECLARE @DatetimeVar DATETIME
DECLARE @VarcharVar VARCHAR(100)
SET @DatetimeVar='01 Jan 2010'
SET @VarcharVar ='Hello'
As you know the COALESCE function returns the first non-null argument value it finds. In this case this
is the datetime value, so the SELECT statement returns the date time :
So far so good, but now for the interesting bit. I swapped the order of the variables in the last line
of the code above :
You might expect this to return the value 'Hello' but it actually generates an error :
Msg 241, Level 16, State 1, Line 17
Conversion failed when converting date and/or time from character string.
Why is this ? Well, the clue is the datatype of the returned value. Rather than using the datatype
of the value actually returned by COALESCE, SQL Server looks at the datatypes of all the values and
uses the one with the highest precedence, which in this case is datetime. SQL Server produces an error
because 'Hello' cannot be converted to a datetime.
This behaviour might seem a little strange in this example, but it makes sense when you think about
how COALESCE works with columns rather than variables. If the datatype returned by COALESCE depended
on which argument was chosen on a row by row basis then we could end up with a mix of datatypes in the
resultset. By doing it this way the datatype returned is predictable, so for instance look at this
query, which uses 3 columns :
SELECT COALESCE(Column1,Column2,Column3) AS DerivedCol
If those 3 columns have a mix of datetime
datatypes then we know that DerivedCol
will always be a datetime, as this has the highest precedence, even though the results of the
query will probably contain a mix of values from Column1, 2 and 3 depending on where the nulls
in the data are.
As with many data type conversion issues some judicious casting and filtering eliminates the error.