COALESCE issues

Category : Blogs Published : April 30, 2012 User Rating : 3.5 Stars      Views : 3.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




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'

SELECT COALESCE(@DatetimeVar,@VarcharVar)
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 :
2010-01-01 00:00:00.000
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 :
SELECT COALESCE(@VarcharVar,@DatetimeVar)
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
FROM
Table1
If those 3 columns have a mix of datetime and varchar 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.
Link back to this article : http://www.sqlmatters.com/Articles/COALESCE issues.aspx

Keywords

Blog,coalesce,null


Comments

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered