Implicit data type conversion can cause both unexpected and undesirable results. Using the same design pattern for one data type and extending it to other data types can get you into trouble if implicit data type conversion occurs.
The following code sample below was inspired by actual legacy code that was intended to build a string showing the values of variables in a stored procedure. The desired output would be something similar to this and was constructed using string concatenation:
@xDATETIME 3-Mar-2013 @xINT 23 @xNVARCHAR something
The problem with building SQL strings using string concatenation is that if any substring is NULL, the entire concatenated string is NULL. COALESCE can fix that problem when the data type is some type of string. The first SELECT demonstrates this.
But the design pattern for dealing with a NULL string variable doesn't extend to other data types as the second and third SELECT statements demonstrate. The DATETIME and INT variables don't get converted to strings (an empty string in this example). Instead, the empty string gets converted to DATETIME or INT, respectively. This conversion yields some unexpected results. The SQL Server documentation here does provide a clue about what happens when an empty string is converted to DATETIME. When a date value is not provided during a data type conversion, SQL Server defaults to 1900-01-01. Clearly a NULL does not provide a date value. Similarly, when a time value is not provided during a conversion, SQL Server defaults to 00:00:00.
I included explicit CAST statements to prove what happens when an empty string is converted to DATETIME or INT.
The solution is to avoid the implicit data type conversions in the first place. CAST the variables to strings first and then pass those strings to COALESCE as shown in the last two statements.
If you run the code, it's easy to see for yourself what's actually going on.
declare @xDATETIME DATETIME;
declare @xINT INT;
declare @xNVARCHAR NVARCHAR(10);
select COALESCE(@xNVARCHAR,''); --returns an empty string
--implicit data type conversions cause
select COALESCE(@xDATETIME,''); --returns 1900-01-01 00:00:00.000
select COALESCE(@xINT,''); --returns 0
--find out what data types the COALESCE
select ISDATE(COALESCE(@xDATETIME,'')); --returns 1 indicating a date value
select ISNUMERIC(COALESCE(@xINT,'')); --returns 1 indicating a numeric value
--explicit data type conversions
select CAST('' AS DATETIME); --returns 1900-01-01 00:00:00.000
select CAST('' AS INT); --returns 0
--how to prevent COALESCE from
doing implicit data type conversion
select COALESCE(CAST(@xDATETIME AS NVARCHAR(23)),''); --returns an empty string
select COALESCE(CAST(@xINT AS NVARCHAR(23)),''); --returns an empty string