SQL Server: Using a case statement to see if a date is NULL and if it is returning ' '

Question

I have a column in my select statement that looks like this:

SELECT CASE WHEN fu.SentOutDate IS NULL THEN '' ELSE fu.SentOutDate END

This returns 1900-01-01 00:00:00.000 for the ones that would otherwise be NULL

I know this because when I put in just fu.SentOutDate it comes up as NULL

Why does this happen and how can I just get it to return a blank value?

Answer 1

A column can only return one data type - DATETIME != string/VARCHAR.

If you want a zero length string in the event of the value being NULL, you have to explicitly change the data type, using CAST/CONVERT to change the non-NULL value to a VARCHAR/etc data type.

Answer 2

Try converting the date to a string so it doesn't try to convert '' to a date:

(CASE WHEN fu.SentOutDate IS NULL THEN '' ELSE CONVERT(varchar,fu.SentOutDate) END) 

Answer 3

It's casting your '' to a DATETIME, since your other column you'd return is a datetime column.

SELECT CASE WHEN 1=1 THEN '' ELSE GETDATE() END 

will give you the same value...

You can convert this to a varchar(32), but I'm not certain of the ramifications

SELECT CASE WHEN 1=1 THEN '' ELSE CAST(GETDATE() AS varchar(32)) END

Answer 4

If you're just checking for NULL values, you might try ISNULL() and cast the date as a varchar.

SELECT ISNULL(CAST(fu.SentOutDate AS VARCHAR(50)), '') AS SendOutDate FROM tablename

Answer 5

It sounds like you're displaying this value in a GUI or client somewhere. In my opinion, the best practice is to convert it from the NULL value there, not in the query.

If you ever create a database that scales to millions of users, you want a little processing as possible in the database and as much as possible in the client. Doing conversion of a date to character is an unneeded load on the system (character calculation is always much slower than math).