cmpmd2 cmpmd2 - 1 month ago 22
SQL Question

SQL Date Return If Null Then Blank

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:

enter image description here

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!

Answer Source

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