Adding text to a value?

Hello!

Hoping this is simple. I have a age SP that does the age from getdate and DOB. I also have it that does months if the year is zero. What I would like to do if the value is 10, and it's a month value, how would I add the "mo" to it. So it reads 10mo. Is this possible?

Thanks!

Rudy

use the "+" operator

@.Months_string = @.months + 'mo'

if @.months is a number field then cast it first:

@.Months_string = cast(@.months as varchar)+ 'mo'|||

You need to cast the number to a character and then just concatenate

selectconvert(varchar(3), @.age)+'mo.'

|||

Code Snippet

select case when yr > 0
then cast(yr as varchar) + ' Yr'
else cast(mo as varchar) + ' Mo'
end as theValue
from ( select 3 as yr, 3 as mo union all
select 1, 5 union all
select 0, 10
) a

/*
theValue
3 Yr
1 Yr
10 Mo
*/

|||

WOW!!

Thank you all! Great suggestions!

Rudy

|||

Ok!

I spoke way too soon! The first mistake I made was to trust I actually wrote the store procedure correct. LOL. It works, but it calculates the age wrong. I guess I was so excited it fiinally worked with out any errors, I didn't check if it was correct. My DOB field is date time, my Age field in nvarchar. He is my feeble attempt. I'm sure there is a better way to write this.

UPDATE Active_Orders

SET Age =CASEWHENdateadd(year,datediff(year, DOB,GetDate()), DOB)

<GetDate()THEN(datediff(year, DOB,GetDate()))- 1 ELSEdatediff(month, DOB,getdate())

% 12 ENDFROM Active_Orders

Any help on this would be greatly appreciated!

Thanks!!

Jim

|||

Jim:

Would something like this work for you:

Code Snippet

UPDATE Active_Orders
SET Age = CASE WHEN year(getdate() - cast(@.dob as datetime)) > 1900
THEN (datediff (year, DOB, GetDate())) - 1
ELSE datediff(month, DOB, getdate()) % 12
END
FROM Active_Orders

|||

Code Snippet

createtable #t1 (namevarchar(25), dob datetime)

insertinto #t1

select'Gomez','02/29/1908'union

select'Morticia','05/01/1936'union

select'Wednesday','10/31/1959'union

select'Friday','12/25/2006'

select*,

age =casewhendatediff(mm, dob,getdate())< 12

--months

thencasewhendatediff(mm, dob,getdate())< 0

then 0

elsedatediff(mm, dob,getdate())

end

else

-- years

datediff(mm, dob,getdate())/12

end

from #t1

So, the update statement would be:

Code Snippet

UPDATE Active_Orders

SET Age =casewhendatediff(mm, dob,getdate())< 12

--months

thencasewhendatediff(mm, dob,getdate())< 0

then 0

elsedatediff(mm, dob,getdate())

end

else

-- years

datediff(mm, dob,getdate())/12

end

FROM Active_Orders