469,112 Members | 2,010 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,112 developers. It's quick & easy.

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!

Jul 23 '05 #1
2 974

<tw*****@gmail.com> wrote in message
news:11**********************@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
Jul 23 '05 #2
Thanks, Simon.

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

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Roy Smith | last post: by
14 posts views Thread by Michael Carr | last post: by
1 post views Thread by Michael Carr | last post: by
26 posts views Thread by the.tarquin | last post: by
3 posts views Thread by Peter | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.