The slight difference between ISNULL and COALESCE

I ran into a little issue yesterday when I was solving an issue for a customer. An Slowly Changing Dimension was implemented for the Organization table and I was looking for the currently active records in that table. I'll explain the issue with the use of the well known AdventureWorks2012 database.

My goal is to select the currently active records in the [HumanResources].[EmployeeDepartmentHistory] table, so I take the next select statement:

This results in 0 rows returned. I'm stunned, because I saw a bunch of records which have a NULL for EndDate so I would expect at least those records to be returned.
I leave out the where clause and add a row so my output also shows the ISNULL(EndDate, GETDATE()) column as follows:









This results in the following output (abbreviated):





I now realize my GETDATE() is converted to a DATE instead of a DATETIME column. But why?
Well, you might say: it was designed that way... as BOL (ISNULL (Transact-SQL)) states:

Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.

Because my EndDate column is of type DATE, the GETDATE() function (inside the ISNULL) will be converted to DATE also. So my where clause will actually look like this:





which is the 'same' as the following, technically spoken it's not the same, because the ISNULL will output a DATE data type, but for explaining my point I will use this :




This will only result in output when you execute the query at exactly 00:00:00.000 hours, which will *never* happen in real time.
Instead of ISNULL I will use COALESCE (Transact-SQL) which, according to BOL,

returns the data type of expression with the highest Data Type Precedence (Transact-SQL)  If all expressions are nonnullable, the result is typed as nonnullable.

This will result in the DATETIME data type for the last expression when the EndDate column is null and returns the correct result set for my goal.



There also is a slight difference when using non-null parameters:

ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.

Hopefully you found this useful and will also never make the same mistake again... :-)