2

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!

cmpmd2
  • 165
  • 1
  • 2
  • 16
  • 1
    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 Aug 22 '17 at 17:47
  • ISNULL (COLUMNNAME, '') in sql server, and check out coalesce as well for learning purpose . – Amit Kumar Singh Aug 22 '17 at 17:50
  • What DBMS are you using? --- I don't understand. SQL Server 2012? – cmpmd2 Aug 22 '17 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 Aug 22 '17 at 17:53
  • @cmpmd2: you have to click the tag after typing part of it, or type it in full – Andomar Aug 22 '17 at 17:55
  • Possible duplicate of [Change NULL values in Datetime format to empty string](https://stackoverflow.com/questions/19930599/change-null-values-in-datetime-format-to-empty-string) – M3talM0nk3y Aug 22 '17 at 17:56

3 Answers3

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

SqlZim
  • 37,248
  • 6
  • 41
  • 59
  • I would change just one thing - use `char(10)` instead of `varchar(10)`, since it's a fixed length. – Zohar Peled Aug 22 '17 at 18:00
  • @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. – SqlZim Aug 22 '17 at 18:07
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,
Andomar
  • 232,371
  • 49
  • 380
  • 404
0

For SQL SERVER

go with

SELECT ISNULL(DOB,'') AS DOB FROM TABLE
Mr. Go
  • 567
  • 1
  • 5
  • 20