SQL Date Return If Null Then Blank

Question

I currently have the following code:

CASE WHEN CONVERT(DATE, BirthDateTime) IS NULL THEN '' END AS DOB,
CASE WHEN CONVERT(DATE, ServiceDateTime) IS NULL THEN '' END AS [Admission Date],
CASE WHEN CONVERT(DATE, DischargeDateTime) IS NULL THEN '' END AS [Discharge Date],

It returns:

What is the best way to still convert the date to Date (original field is datetime) and if it is Null then return blank or ' '

Thanks in advance!

Solution

In SQL Server:

Since you want to use an empty string instead of null, then you are going to be converting to a string. You can use the size of 10 along with style 120 to return iso format (without the time portion) like so:

select 
    dob = isnull(convert(varchar(10),BirthDateTime,120),'')
  , [Admission Date] = isnull(convert(varchar(10),ServiceDateTime,120),'')
  , [Discharge Date] = isnull(convert(varchar(10),DischargeDateTime,120),'')
from ...

You can find the other style codes for convert() here: docs: cast and convert

Comments

[Ken White]

What DBMS are you using? When you were writing your question and added the SQL tag, a large box was shown to you that suggested you also add a tag for the specific DBMS, because syntax and functionality differ between them. Why did you decide to ignore that suggestion?

[Amit Kumar Singh]

ISNULL (COLUMNNAME, '') in sql server, and check out coalesce as well for learning purpose .

[cmpmd2]

What DBMS are you using? --- I don't understand. SQL Server 2012?

[cmpmd2]

When you were writing your question and added the SQL tag, a large box was shown to you that suggested you also add a tag for the specific DBMS, because syntax and functionality differ between them. Why did you decide to ignore that suggestion? --- I have been tagging them. I don't know why it doesn't show.

[Andomar]

@cmpmd2: you have to click the tag after typing part of it, or type it in full

[M3talM0nk3y]

Possible duplicate of Change NULL values in Datetime format to empty string

[Andomar]

That won't work, both arguments to isnull must have the same type

[Zohar Peled]

I would change just one thing - use char(10) instead of varchar(10), since it's a fixed length.

[SqlZim]

@ZoharPeled Usually I would, but since null is being converted to an empty string, and the conversion would change the empty string into 10 blank spaces... I just don't like a bunch of space characters in my result set instead of an empty string.