Now the answer is NEVER.
Without the RTRIM then the ansewer : IF NULL is right.
It surprished me a bit when I use this code :
DECLARE @anyDateTime date = null
IF (COALESCE(@anyDateTime,'')) = ''
print '*' + cast(@anydatetime as varchar) + '*'
Why true ? The COALESCE(@anyDateTime,'') returns 1900-01-01 which is not ''
There is already a fairly good explanation in the answer of the QotD. COALESCE (like many functions and operators in SQL Server), when confronted with mixed data types in the input, will implicitly convert values to be of the same data type and then return that data type. To determine which data type is used, the rules of data type precedence (https://msdn.microsoft.com/en-us/library/ms190309.aspx) are used. In the case of this example, the input data types are date and varchar(1). Date has a higher precedence, so the empty string is implicitly converted to date. This conversion results in Jan 1st, 1900, so the COALESCE returns a date value that is either @anyDateTime or, if that is NULL, Jan 1st, 1900.
In the QotD, the next step is RTRIM. This is a specific string function, so it will always return a string and convert its input to string. Jan 1st 1900 gets casted as string, resulting in '1900-01-01' or (depending on whether the data type used is date or datetime, and on the language settings) some other representation of that date. If @anyDateTime is not null, it will return a similar representation of whatever date is in that variable. Bottom line is that this is never equal to the empty string.
In your example, you omit the RTRIM. So the result of COALESCE (which was a date value) is compared to '' (a varchar(1) value). SQL Server again consults the rules of data type precedence and then implicitly converts the empty string to a date - again resulting in Jan 1st, 1900. Which makes the comparison true.
Interesting other queries to run:
SET LANGUAGE English;
DECLARE @anyDateTime date = NULL;
IF COALESCE(@anyDateTime, '1/1/1900') = '' PRINT 'Equal'; ELSE PRINT 'Different';
IF COALESCE(@anyDateTime, '') = '19000101' PRINT 'Equal'; ELSE PRINT 'Different';
IF COALESCE(@anyDateTime, 'Jan 1, 1900') = '1-1-1900' PRINT 'Equal'; ELSE PRINT 'Different';
(If you run with a different language setting, some of the implicit string-to-date conversioins might fail)