asked
How do I write multiple case statements in one single statement?
case WHEN len(price_dt)>25 THEN SUBSTRING(price_dt,1,10) WHEN len(price_dt)=13 THEN REPLACE(price_dt,'.','') WHEN len(price_dt)=11 THEN REPLACE(price_dt,'**','') --WHEN price_dt like '%est' THEN cast(convert(varchar,cast( replace( price_dt,'est','') as datetime), 112) as datetime) WHEN price_dt like '%st%' THEN cast(convert(varchar,cast( replace( price_dt,'st','') as datetime), 112) as datetime) WHEN price_dt like '%nd%' THEN cast(convert(varchar,cast( replace( price_dt,'nd','') as datetime), 112) as datetime) WHEN price_dt like '%rd%' THEN cast(convert(varchar,cast( replace( price_dt,'rd','') as datetime), 112) as datetime) WHEN price_dt like '%th%' THEN cast(convert(varchar,cast( replace( price_dt,'th','') as datetime), 112) as datetime) --WHEN len(price_dt)=19 THEN SUBSTRING(REPLACE(REPLACE(price_dt,'00:00:00',''),' ','/') ,1,(LEN(REPLACE(REPLACE(price_dt,'00:00:00',''),' ','/') )-1))
Yes didnt get the question, your statement looks all right, just add else condition and then end as column name
That is what a CASE expression looks like (although you forgot the END at the end of yours) [
https://msdn.microsoft.com/en-gb/library/ms181765.aspx][1] Also you may want to stick an ELSE on the end of it just to catch anything that the WHENs don't [1]:
https://msdn.microsoft.com/en-gb/library/ms181765.aspx
I assume those WHENs in the middle are getting rid of the letters on a date i.e 1st, 2nd, 3rd, 4th if you wanted to put it in a single statement you could do nested REPLACE, but what you have looks fine. (also this would give you a different output to what you are currently getting if you had something like "1strd" so it) cast(convert(varchar,cast( replace( replace( replace( replace( price_dt,'est',''),'st',''),'nd',''),'rd','') ,'th','') as datetime), 112) as datetime)
21 People are following this question.
