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 : 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 : 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 : 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.