smalldatetime,varchar and CASE

9 Dec 2005 3:43 PM

James Carters

Have a table with an end_date column (smalldatetime) where a NULL shows that
the row is still active. I need to be able to show the status (when it
closed, or present) so tried this and variations

end_date =
CASE
WHEN CAST(jv.[end_date] AS varchar)IS NULL THEN ' present '
ELSE CAST(jv.[end_date] AS varchar)
END,

But it's not working, any thoughts?

9 Dec 2005 3:52 PM

Stijn Verrept

James Carters wrote:

> end_date =
> CASE
> WHEN CAST(jv.[end_date] AS varchar)IS NULL THEN ' present '
> ELSE CAST(jv.[end_date] AS varchar)
> END,
>
> But it's not working, any thoughts?

How about

end_date = case when jv.End_date IS NULL then ' present ' else
CAST(jv.[end_date] AS varchar(16)) end

--

HTH,

Stijn Verrept.

9 Dec 2005 6:17 PM

Trey Walpole

what do you mean by "it's not working"?
you're not getting ' present ' for null dates? you're getting errors?

the given example should return as expected, so i assume the problem is
something else

James Carters wrote:
Show quote

> Have a table with an end_date column (smalldatetime) where a NULL shows that
> the row is still active. I need to be able to show the status (when it
> closed, or present) so tried this and variations
>
> end_date =
> CASE
> WHEN CAST(jv.[end_date] AS varchar)IS NULL THEN ' present '
> ELSE CAST(jv.[end_date] AS varchar)
> END,
>
> But it's not working, any thoughts?
>
>