Bizarre case behavior.

I'm doing a select which includes the following:

case
when (rtrim(ltrim(T464.COMMENT_4)) = '' or T464.COMMENT_4 is null)
then ''
else
convert(datetime,left(replace(replace(T464.COMMENT _4,' QTR: ',''),' -
',''),10))
end as QuarterStartDate

The COMMENT_4 field is a char(51), and contains values like: ' QTR:
03/01/2005 - 05/31/2005', '', a number of spaces, or NULL. If I remove
the convert to datetime within the case, data returns as expected,
retaining NULLs and blanks. If I leave the convert in, it converts
every row to datetime and seems to disregard the case expression.

I've attempted converting COMMENT_4 to a varchar first, and that didn't
help either. Also this does not seem to be affected by removal of the
LTRIM and RTRIM. Googling for this turns up no similar results. Have
I simply nested too far within the case, or what?

Any and all help and advice would be greatly appreciated.

Thanks!<twbanks@.gmail.com> wrote in message
news:1109020542.481788.195910@.c13g2000cwb.googlegr oups.com...
> I'm doing a select which includes the following:
> case
> when (rtrim(ltrim(T464.COMMENT_4)) = '' or T464.COMMENT_4 is null)
> then ''
> else
> convert(datetime,left(replace(replace(T464.COMMENT _4,' QTR: ',''),' -
> ',''),10))
> end as QuarterStartDate
> The COMMENT_4 field is a char(51), and contains values like: ' QTR:
> 03/01/2005 - 05/31/2005', '', a number of spaces, or NULL. If I remove
> the convert to datetime within the case, data returns as expected,
> retaining NULLs and blanks. If I leave the convert in, it converts
> every row to datetime and seems to disregard the case expression.
> I've attempted converting COMMENT_4 to a varchar first, and that didn't
> help either. Also this does not seem to be affected by removal of the
> LTRIM and RTRIM. Googling for this turns up no similar results. Have
> I simply nested too far within the case, or what?
> Any and all help and advice would be greatly appreciated.
> Thanks!

A CASE expression can only return a single data type, which may be part of
your problem, but rather than guessing at exactly what your data looks like,
I suggest you provide a sample - if someone else can quickly copy and paste
some code into Query Analyzer, you are much more likely to get a useful
reply:

http://www.aspfaq.com/etiquette.asp?id=5006

Simon|||Thanks, Simon.

I wasn't thinking about that. Adding another convert to turn it into a
varchar did the trick.