Convert NULL datetime to Blank

Question

I would like to convert a datetime field to varchar so I can show a blank or a message when the date is NULL. This is what I have so far:

select
isnull(us.Date,0) as USDate,
isnull(au.Date,0) as AUDate
from ustable us
left join autable au
on us.column=au.column

Right now this is showing:

USDATE 
2014-10-24 10:29:07.450
1900-01-01 00:00:00.000

I would like to be able to make the "1900-01-01 00:00:00.000" varchar so I can write a message or show a true blank.

Answer 1

The simplest line of code I've found for what you're trying to accomplish is as follows.

ISNULL(CONVERT(VARCHAR(30),us.Date,121),'') as USDate,
ISNULL(CONVERT(VARCHAR(30),au.Date,121),'') as AUDate,

I have tested this and verified that it works.

Answer 2

To do it in SQL:

Change your SELECT statement to incorporate a WHEN...THEN clause:

CASE us.Date 
  WHEN '1900-01-01 00:00:00.000' THEN ''
  WHEN NULL THEN ''
  ELSE us.Date
END as USDate,
CASE au.Date 
  WHEN '1900-01-01 00:00:00.000' THEN ''
  WHEN NULL THEN ''
  ELSE au.Date
END as AUDate

If you have front-end code, then you could just implement the functionality on the front-end without changing your SQL code.

Answer 3

Given a column defined like this:

date_of_birth datetime null

You can simply say

select date_of_birth = coalesce( convert(varchar(32),date_of_birth) , '' )
from some_table_with_a_nullable_datetime_column

Answer 4

You can use Case AND CONVERT like this -

SELECT
CASE WHEN us.Date IS NULL THEN '' ELSE CONVERT(VARCHAR(30), us.Date, 121) END AS USDate,
CASE WHEN au.Date IS NULL THEN '' ELSE CONVERT(VARCHAR(30), au.Date, 121) END AS AUDate
FROM ustable us
left join autable au
on us.column=au.column

Answer 5

You can use the NVL() function ...

You can use the NVL function to convert an expression that evaluates to NULL to a value that you specify. The NVL function accepts two arguments: the first argument takes the name of the expression to be evaluated; the second argument specifies the value that the function returns when the first argument evaluates to NULL. If the first argument does not evaluate to NULL, the function returns the value of the first argument.

For example:

select
nvl(us.Date,'') as USDate,
nvl(au.Date,'') as AUDate
from ustable us
left join autable au
on us.column=au.column

Or put anything you want as the Default:

select
nvl(us.Date,'this was a NULL') as USDate,
nvl(au.Date,'this was a NULL') as AUDate
from ustable us
left join autable au
on us.column=au.column