Display of date time inforamtion - some columns are NULL some are not

I have a column in a table that is a datetime data type. Some columns
are NULL some are not.
So, a sampling of data could include:
NULL
2005-06-06 12:32:53.000
2005-04-12 11:32:53.000
NULL
NULL
2005-12-22 12:32:53.000
When I select from this column, if the value is NULL, I need to replace
NULL with the word 'No'. If the value is not NULL, then I need to
display the date - so my output needs to look like this:
No
06/06/2005
04/12/2005
No
No
12/22/2005
I know how to convert the date - the problem I am having is converting
the NULL datetime to characters and including the logic to account for
NULLs in the first place.
I suspect that I need a CASE statement, but I am not sure how to
accomplish this.
Thanks-SELECT COALESCE(CONVERT(CHAR(10), datecolumn, 101), 'No')
FROM table
However, I recommend against using ambiguous formats like m/d/y for display.
<wxbuff@.aol.com> wrote in message
news:1138713642.280962.50970@.g43g2000cwa.googlegroups.com...
>I have a column in a table that is a datetime data type. Some columns
> are NULL some are not.
> So, a sampling of data could include:
> NULL
> 2005-06-06 12:32:53.000
> 2005-04-12 11:32:53.000
> NULL
> NULL
> 2005-12-22 12:32:53.000
> When I select from this column, if the value is NULL, I need to replace
> NULL with the word 'No'. If the value is not NULL, then I need to
> display the date - so my output needs to look like this:
> No
> 06/06/2005
> 04/12/2005
> No
> No
> 12/22/2005
> I know how to convert the date - the problem I am having is converting
> the NULL datetime to characters and including the logic to account for
> NULLs in the first place.
> I suspect that I need a CASE statement, but I am not sure how to
> accomplish this.
> Thanks-
>|||
select coalesce(convert(varchar(10),thedatecolu
mn,101),'No')
However, you may be better off doing the formatting in your client|||Aaron -
Perfect! I am still pretty new at this and was unfamiliar with the
COALESCE function. I see from the BOL that it replaces more
complex CASE statements so I am gratified to know that I was thinking
down the right path. Thank you for helping - it will save
me a great deal of time.
Danielle