Using ISNULL or COALESCE on date column

I’ve got a date column where some rows have got NULL values. I would like to use ISNULL or something like that to substitute those values with something like ‘N/A’, however, when I try to use ISNULL, I get an error due to two different data types. If I try to convert my Date column to VARCHAR in order to be able to use ISNULL, then the way my column displays dates gets distorted. Is there any way to solve this problem?

ISNULL(DateSent, 'N/A')

Answer

I recommend COALESCE(), because it is standard. However, your problem is that the first column is a date/time and that is not compatible with a string.

So, you need to convert the value. You can use the default format:

COALESCE(CONVERT(VARCHAR(255), DateSent), 'N/A')

Or you can add a conversion argument:

COALESCE(CONVERT(VARCHAR(255), DateSent, 120), 'N/A')

Or you can use FORMAT() for more flexibility.