Coalesce and Case

Question

Coalesce and Case

dsmith402

SSC Enthusiast

Points: 128
More actions
November 29, 2016 at 4:04 pm

#337808

I've seen this used, matter of fact staring at a query with one right now, BUT when I attempt to re-create for my own personal use every time I receive the error
"Conversion failed when converting date and/or time from character string."
Will someone please help me understand how to fix this Case Expression
,coalesce(case WHEN FSI.FID IN ('1800','1810')
and FSI.StepCompletion is not null
THEN FSI.StepCompletion
END,'Not Completed') [SaleHeld]

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 77014 More actions · Answer 1

You have both a CASE expression and a COALESCE. For each of those, the possible values must be of compatible data types.

,coalesce(case WHEN FSI.FID IN ('1800','1810')

and FSI.StepCompletion is not null

THEN FSI.StepCompletion

END,'Not Completed') [SaleHeld]

Since FSI.StepCompletion is datetime (based on the error), all of the other values must be compatible with datatime. 'Not Completed' cannot be converted to a valid datetime value, so you are getting an error.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

dsmith402 SSC Enthusiast Points: 128 More actions · Answer 2

Ok, that certainly makes sense. But how would this expression be re-written in order for it to work?

DesNorton SSC-Insane Points: 24569 More actions · Answer 3

replace FSI.StepCompletion

with CONVERT(VARCHAR(30), FSI.StepCompletion)

How to post data/code on a forum to get the best help.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

dsmith402 (11/29/2016)
Ok, that certainly makes sense. But how would this expression be re-written in order for it to work?

Drop the COALESCE, it's redundant and confusing.

CASE

WHEN FSI.FID IN ('1800','1810') AND FSI.StepCompletion IS NOT NULL THEN CONVERT(FSI.StepCompletion to your requirements)

ELSE 'Not Completed' END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

drew.allen SSC Guru Points: 77014 More actions · Answer 5

dsmith402 (11/29/2016)
Ok, that certainly makes sense. But how would this expression be re-written in order for it to work?

It depends on what you are trying to solve, which is why I didn't suggest any workarounds. Converting your datetime to (N)(VAR)CHAR as others have suggested is one possible workaround, but it might not be the right one for your needs. Returning a NULL value instead of 'Not Completed' is another possible workaround. Returning an artificial date (such as 9999-12-30 or 9999-01-01) instead of 'Not Completed' is another possible workaround. We don't know how the results will be used, so we can't tell you which of those is the best option.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

dsmith402 SSC Enthusiast Points: 128 More actions · Answer 6

DesNorton (11/29/2016)
replace FSI.StepCompletion
with CONVERT(VARCHAR(30), FSI.StepCompletion)

Interesting, I wasn't expecting this to be that easy of a solution! Thanks everyone!