question

sam09 avatar image
sam09 asked

how to change the format of the numeric values to be numeric with 2 decimal places

how to change the format of the numeric values to be numeric with 2 decimal places. The current layout shows them in text with leading zero’s code in procedure is SUBSTRING(data,232,11)as [REFERED_AMT], SUBSTRING(data,232,11)as [PRINCIPLE_AMT], 13642 ,i want it to be 136.42 ![alt text][1] [1]: /storage/temp/3148-1.png
proc
1.png (4.3 KiB)
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sam09 avatar image sam09 commented ·
cast((cast(SUBSTRING(data,232,11) as float)/100.0) as numeric(18,2)) as [REFERED_AMT], im getting this error while editing sp Error converting data type varchar to numeric.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question. You can mark your own answer as the solution.
0 Likes 0 ·
sam09 avatar image sam09 commented ·
i have CONVERT(varchar,SUBSTRING(data,232,11) /1) as [REFERED_AMT], 13642 And i want it to be 136.42
-1 Like -1 ·
Show more comments
Gazz avatar image
Gazz answered
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
INT won't work as you can't have decimal points with an INT. The MONEY data type though would work out great if it's currency that's being represented here. Good solution. Better than the one that I came up with.
1 Like 1 ·
Gazz avatar image Gazz commented ·
oh yeh, silly me. At least what you posted would have actually worked!
1 Like 1 ·
JohnM avatar image
JohnM answered
If I understand correctly, would something like this work? You could wrap this around the substring to represent @amt. DECLARE @amt VARCHAR(100) = '12311510000001364212123123' SELECT LEFT(CAST(SUBSTRING(@amt,8,11) AS NUMERIC),LEN(CAST(SUBSTRING(@amt,8,11) AS NUMERIC)-2))+'.'+RIGHT(SUBSTRING(@amt,8,11),2) You can then cast/convert the final value to whatever numeric data type you want. Hope that helps!!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sam09 avatar image
sam09 answered
Thanx every1 i have solved it CAST(cast((cast(SUBSTRING(data,232,11) as float)/100.0) as numeric(18,2)) as varchar) as [REFERED_AMT],
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aas avatar image
aas answered
select format((13264/100.0),'n2') as [REFERED_AMT]
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.