cast does not work in case statement

Question

Post reply

cast does not work in case statement

timscronin

SSCertifiable

Points: 6851
More actions
December 28, 2010 at 7:24 am

#388514

I had a developer show me some tsql which is puzzling. He does a cast with inline and it works fine, in the case we get the error

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

CASE WHEN h.TSITEM_TYP_NAM <> '&TotaledSpan'

THEN v.ENTEREDONDTM

-- WHEN h.TSITEM_TYP_NAM = '&TotaledSpan'

-- THEN CAST(spe.ENTEREDONDTM AS varchar(25)) + ' \ ' + CAST(epe.ENTEREDONDTM AS varchar(25))

ELSE COALESCE(t.STRT_DTM, t.END_DTM)

END AS EditingDt, CAST(spe.ENTEREDONDTM AS varchar(25)) + ' \ ' + CAST(epe.ENTEREDONDTM AS varchar(25)) as testdate

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

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

toddasd SSCertifiable Points: 6298 More actions · Answer 1

toddasd

SSCertifiable

Points: 6298

December 28, 2010 at 8:26 am

#1266757

Some sample data and DDL please.

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

timscronin SSCertifiable Points: 6851 More actions · Answer 2

sorry here is sample data from concatenation

Nov 1 2010 1:29PM \ Nov 1 2010 11:13AM

Nov 9 2010 8:23AM \ Nov 11 2010 8:46AM

Nov 11 2010 8:46AM \ Nov 9 2010 8:26AM

Nov 9 2010 8:24AM \ Nov 9 2010 11:34AM

THe 2 fields involved are both datetime. If the concatenation is done with the case it fails, as a straight select it works

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 3

Hi,

Have a look at this

declare @Temp table(Rid int identity,Col1 varchar(20),ddate datetime)

Insert into @Temp

Select 'Hi',GETDATE()

Union all

Select 'This is',GETDATE()

Union all

Select 'Test',GETDATE()

-- Case :1

Select Case When Col1='Hi' then 'Hello'+' '+CAST(ddate AS varchar(25)) else Col1 end from @Temp

-- Case :2

Select Case When Col1='Hi' then 'Hello'+' '+CAST(ddate AS varchar(25)) else ddate end from @Temp

Here in case:1 will work since we are converting varchar value to varchar where as in case:2 it is datetime column where we cant do conversion with varchar to column

Thanks

Parthi

Thanks
Parthi

Charles Hearn SSCrazy Points: 2680 More actions · Answer 4

You need to make sure that all of the values returned by the case expression are of the same data type. It appears that you are mixing a datetime data type with a varchar, causing the error. Try something like below:

SELECT CASE



	WHEN h.TSITEM_TYP_NAM <> '&TotaledSpan' THEN CAST(v.ENTEREDONDTM AS VARCHAR(25))

	WHEN h.TSITEM_TYP_NAM = '&TotaledSpan'  THEN CAST(spe.ENTEREDONDTM AS varchar(25)) + ' \ ' + CAST(epe.ENTEREDONDTM AS varchar(25))

ELSE COALESCE(CAST(t.STRT_DTM AS VARCHAR(25)),CAST(t.END_DTM AS VARCHAR(25))) END AS EditingDt

GSquared SSC Guru Points: 260824 More actions · Answer 5

Are you building a dynamic SQL query? It looks like it. If so, then the datetime variable may not be properly enclosed in single-quotes.

If that's not the situation, what is the value of "&TotaledSpan" at runtime?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Abhijeet Chavan Default port Points: 1491 More actions · Answer 6

Abhijeet Chavan

Default port

Points: 1491

December 28, 2010 at 10:10 pm

#1267016

It seems like you have 2 different columns EditingDt and testDate.

So case in one column has nothing to do with cast in another column.

What is the datatype of t.STRT_DTM, t.END_DTM , (by the name i think it is datetime)??

Please post a test script so that we all can try to help u better.