SQLTeam.com | Weblogs | Forums

How to Return Space if Column Has NULL Value

Transact-SQL
#1

hi experts,
A column on one of my reports contains NULL. I would prefer to have the query return blank for that column rather than NULL.

I tried COALESCE(MyColumn, '') but that returns 1900-01-01 00:00:00.000 Note that the column data type is datetime.

How can I do this in my Select statement?
Thanks very much.

#2

You can't force a datetime to be blank since blank is incompatible with date (i.e. blank is not a valid date).

If you want to be able to get a blank, you need to convert the date to a string. For example:

COALESCE(CONVERT(varchar(30), MyColumn, 120), '')

1 Like
#3

and you want to do that because what happens in the report?

#4

If it's only going to be blank in a report, like SSRS, you can apply formatting on the report element to display nothing if the date value is NULL. Otherwise @ScottPletcher 's suggestion is best.

1 Like
#5

The reason you get 1900-01-01 for a blank (empty string) date is due to implicit conversion. That blank value is implicitly converted to a 0 and the zero date is 1900-01-01.

The question is why do you want it to be blank? Where and how are you displaying this data - is it a report? Or are you copy/pasting into Excel - something else?

#6

@jeffw8713 Yes ultimately it gets copied/pasted into excel. It's just confusing the user, that is the only reason. Thanks

#7

If you use copy/paste from SSMS to Excel - then you have 2 options:

  1. Return the NULL value - then use Ctrl-H to find the word NULL and replace with blank.
  2. Convert the column to a string in your code as outlined by Scott.

Note: for Excel - you want to make sure you remove milliseconds from the value returned. If you include milliseconds then you have to reformat the column and apply a date format - or it only shows the time. The easiest way to do that is convert the datetime to a smalldatetime - then convert to string.

I generally don't bother with returning strings for dates - just convert to smalldatetime and take the extra second in Excel to replace all NULL strings with a blank.

1 Like
#8

That will work fine for my needs, Thanks @jeffw8713

#9

SmallDateTime has the nasty habit of rounding up to the next day when you least expect it. Don't use SmallDateTime. Use the approach that @ScottPletcher posted, which also completely solves your problem on the copy'n'paste thing.

#10

Actually - that doesn't solve all the issues with copy/paste. The issue with Scott's version is that it includes milliseconds, which forces Excel to display just the time element of the date/time.

If you are concerned about that conversion - then use DATETIME2 with an appropriate precision for your data. If you must include the milliseconds in Excel, then you are going to be reformatting the column anyways so including a simple find-replace to remove the string NULL isn't too much extra effort.