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

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

Do the formatting where it belongs - on the client. In SQL you could use
CONVERT but then you loose the domain.
Another way would be to use sql_variant which retains the datatype for later
use, but that's just another complication that can be successfully avoided b
y
formatting the data on the client.
ML
http://milambda.blogspot.com/BCP doesn't have any formatting control logic built in.
Before making definitive statements you need to know more about what the
poster is trying to achieve.
I've done formatting lots of time in the SQL Server because thats been the
best place for that particular problem, for instance creating the output
file for a data feed, its very easy (a couple of lines of T-SQL) to create a
table and use BCP to output it to a file that can then be sent out to
another part of the company/supplier.
A statement like 'do the formatting where it belongs - on the client' is
meaningless in this instance.
Its more true to say 'Do the formatting where it makes sense and is most
efficient for the problem you are trying to solve'.
Sorry if the post sounds harsh, I'm a bit fed up with this do all formatting
on the client debarkle.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"ML" <ML@.discussions.microsoft.com> wrote in message
news:A73D446B-49C0-429A-B30C-EB11A035B5E7@.microsoft.com...
> Do the formatting where it belongs - on the client. In SQL you could use
> CONVERT but then you loose the domain.
> Another way would be to use sql_variant which retains the datatype for
> later
> use, but that's just another complication that can be successfully avoided
> by
> formatting the data on the client.
>
> ML
> --
> http://milambda.blogspot.com/