More dates issues

Hello,
The dates in a SQL2k table are stored as nvarchar (yyymmdd). When displayed,
the CAST (DATE_FIELD AS datetime) syntax is used. Because of the given
format, when the DATE_FIELD has no dates, the empty field is converted into
01/01/1900. What is needed to keep 01/01/1900 from desplaying in a query and
keeping the field empty?
TIA
Mike

"Mike" wrote

The dates in a SQL2k table are stored as nvarchar (yyymmdd). When displayed,
the CAST (DATE_FIELD AS datetime) syntax is used. Because of the given
format, when the DATE_FIELD has no dates, the empty field is converted into
01/01/1900. What is needed to keep 01/01/1900 from desplaying in a query and
keeping the field empty?

Hi Mike,

Did you try using CASE/WHEN?

CASE
WHEN (DATE_FIELD IS NOT NULL)
THEN CAST (DATE_FIELD AS datetime)
ELSE NULL
END

But are you sure you want to do this? I think
I would prefer 1/1/1900 to Null, but you
know your situation best.

Good luck,

Gary Walter