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!

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? — Ken White
– Ken White, Commented Aug 22, 2017 at 17:47
ISNULL (COLUMNNAME, '') in sql server, and check out coalesce as well for learning purpose . — Amit Kumar Singh
– Amit Kumar Singh, Commented Aug 22, 2017 at 17:50
What DBMS are you using? --- I don't understand. SQL Server 2012? — cmpmd2
– cmpmd2, Commented Aug 22, 2017 at 17:53
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. — cmpmd2
– cmpmd2, Commented Aug 22, 2017 at 17:53
@cmpmd2: you have to click the tag after typing part of it, or type it in full — Andomar
– Andomar, Commented Aug 22, 2017 at 17:55

SqlZim · Accepted Answer · 2017-08-22 17:51:39Z

4

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

answered Aug 22, 2017 at 17:51

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zohar Peled Over a year ago

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

SqlZim Over a year ago

@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.

Andomar · Accepted Answer · 2017-08-22 17:52:22Z

0

You could use coalesce. A column must always have one type regardless of row, so you have to convert your date type to a text type before you pass it to coalesce:

COALESCE(CONVERT(VARCHAR(100), BirthDateTime), '') AS DOB,

answered Aug 22, 2017 at 17:52

Andomar

239k55 gold badges387 silver badges412 bronze badges

Comments

Mr. Go · Accepted Answer · 2017-08-22 17:55:44Z

0

For SQL SERVER

go with

SELECT ISNULL(DOB,'') AS DOB FROM TABLE

answered Aug 22, 2017 at 17:55

Mr. Go

5571 gold badge5 silver badges20 bronze badges

1 Comment

Andomar Over a year ago

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

Collectives™ on Stack Overflow

SQL Date Return If Null Then Blank

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related